DAX for Power BI Part 4.4 - Using Variables

By Andrew Gould

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:

This video explains how to use variables in DAX to break a large calculation into discrete parts. You'll learn how to declare and assign values to variables in calculated columns and measures, and how to refer to variables in your code. You'll see how to declare variables within a function to create variables with a limited scope, and how to create variables which can hold the results of a table expression.

Chapters
00:00 Topic list
00:30 Creating a Calculated Column
03:52 Declaring and Using DAX Variables
07:03 Using Multiple Variables
08:04 Attempting to Change Variable Values
09:43 Testing for Blanks
11:41 Nesting Variables
14:37 Creating a Measure
16:22 Using Variables in Measures
17:56 Excluding Blanks
21:51 Using Table Variables

[wayneedmondson]

Awesome Andrew! I've always wanted to gain a better understanding of using variables in DAX. Your explanations are clear and easy to follow. Thanks a million! Thumbs up!!

[tejamarneni]

Thank you, Andrew. This is what I was waiting for.

[mePrafull]

Thank you very much for the detailed explaination of varaibles, very much helpful.

[saadahmedqureshi]

Very well explained. Thank you so much.

[jerrydellasala]

This excellent video inspired me to join to get access to the rest of this playlist. I had almost done it the last time you made one available to non-members, but just didn't pull the trigger. Anyway, I'm still stuck on this one. The rest are unavailable, even the ones listed in the Members section. I joined at the chick level. Do I need to upgrade, or does it just take a while for it to kick in? Seems to be OK on the Members page.
Thanks for some great videos!

[nelrosell]

we can also do like this OR(ISBLANK(Movies[Box Office]), Movies[Budget]) as it's only two values are evaluated rather than using two pipes || for OR condition

[nikakalichava]

Hello! Thank you again, but I got a little confused at 20:26.
So SUMX has 2 arguments 1 - TABLE and 2 - EXPRESSION.
As First argument "TABLE "we use CALCULATETABLE, an expression...
And as second argument of "EXPRESSION" we used a column??
Am I missing anything? How come?
EDIT*:
I feel like the measure should b like this :
VAR Budget = SUMX(
CALCULATETABLE(
Movies,
NOT ISBLANK(Movies[Box Office]),
NOT ISBLANK(Movies[Budget])
),
SUM(Movies[Budget])
)

How can we use a column as an EXPRESSION argument?

[qasimali-guoz]

19:29 I was expecting FILTERS instead of CALCULATETABLE. Can you explain why can't we use FILTERS here instead of CALCULATETABLE?

[mydreams]

Sir I couldnt join your channel. It shows me error.

[AmitSharma-pozb]

Hey Andrew, I am getting a VBA error "method open of object Recordset" failed to connect..there's an SQL statement written to open a SharePoint but it's not working..need ur expertise here pls

[jaichander]

Hello sir! Is it a good idea to use VBA code (similar to vlookup) on excel file containing 5 lack rows?

[stickercode]

Mostly lost in the last 5 minutes. I think, I need to take a break since watching 3 videos in row causes headaches