Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi!
I have a calculated table that tells me if the client has sold more than 25K in the last 3 Quarters.
When I use variables, it doesn't work, when I don't use variables, it does....
Why??? It looks ugly without the variables...
With Variables:
Normalize =
VAR Qm1 =
CALCULATE (
[SO Net],
PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
)
VAR Qm2 =
CALCULATE (
[SO Net],
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
)
VAR Qm3 =
CALCULATE (
[SO Net],
PREVIOUSQUARTER (
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
)
)
RETURN
SUMMARIZECOLUMNS('DB'[End User Name (or Next Purchasing Entity)],
"Normalize",
SWITCH (
TRUE (),
CALCULATE (
[SO Net],
PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
) > 25000
&& CALCULATE (
[SO Net],
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
) > 25000
&& CALCULATE (
[SO Net],
PREVIOUSQUARTER (
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
)
) > 25000, "Large Clients (Exclude to normalize)",
"Other Clients"
)
)
Without Variables:
Normalize =
SUMMARIZECOLUMNS (
'DB'[End User Name (or Next Purchasing Entity)],
"Normalize",
SWITCH (
TRUE (),
CALCULATE ( [SO Net], PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) ) > 25000
&& CALCULATE (
[SO Net],
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
) > 25000
&& CALCULATE (
[SO Net],
PREVIOUSQUARTER (
PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
)
) > 25000, "Large Clients (Exclude to normalize)",
"Other CLients"
)
)
Solved! Go to Solution.
Hi @Anonymous ,
Looking at the two measures you wrote separately (with and without variables), the first measure with variables, although variables are defined, but does not use it in the later calculations. It seems that the formulas are the same except for the defined variable in these two measures. It is reasonable to assume that the two measures will return the same results...
You can check the following link to learn more about the use and caution of variables. Variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.
Use variables to improve your DAX formulas
Caution When Using Variables in DAX and Power BI
Best Regards
Hi @Anonymous ,
Looking at the two measures you wrote separately (with and without variables), the first measure with variables, although variables are defined, but does not use it in the later calculations. It seems that the formulas are the same except for the defined variable in these two measures. It is reasonable to assume that the two measures will return the same results...
You can check the following link to learn more about the use and caution of variables. Variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.
Use variables to improve your DAX formulas
Caution When Using Variables in DAX and Power BI
Best Regards
You are defining the variables but are not using them anywhere?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
140 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
209 | |
92 | |
64 | |
59 | |
56 |