Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a measure called THRESHOLD, where [THRESHOLD] = AVERAGE ( [INVOICE AMOUNT] ) - 12500. The currently value of THRESHOLD is 6000.
If I create a column called TEST1, where TEST1 = IF ( [INVOICE AMOUNT] < 6000 , "YES" , "NO" ). There are 25 records with "YES" values, which is the correct count.
However, if I create a column called TEST2, where TEST2 = IF ( [INVOICE AMOUNT] < [THRESHOLD] , "YES" , "NO" ), I get 0 record with "YES".
Any idea why TEST2 isn't working as intended?
Solved! Go to Solution.
@TD21 - It is generally a bad idea to use a measure in a calculated column. There are 2 reasons why
1. Row context
2. Calculated columns are not dynamic
Now, in your case the Row context is the problem. Let's see, what is the AVERAGE of a singe row in your INVOICE AMOUNT table? Hmm, that would be the value for [INVOICE AMOUNT] in that row. The AVERAGE of a single value is the value. Hence, that row's [INVOICE AMOUNT] can never be less than itself and definitely won't be less than itself minus some number and hence why you get NO for everything.
Now, in your case, if you are not worried about 2, then you could get around this by changing your THRESHOLD formula to:
[THRESHOLD] = CALCULATE(AVERAGE ( [INVOICE AMOUNT] ),ALL('Table')) - 12500
or
[THRESHOLD] = AVERAGEX ( ALL('Table'),[INVOICE AMOUNT] ) - 12500
Second formula edited thanks to @CNENFRNL catching my copy and paste error!
@TD21 - It is generally a bad idea to use a measure in a calculated column. There are 2 reasons why
1. Row context
2. Calculated columns are not dynamic
Now, in your case the Row context is the problem. Let's see, what is the AVERAGE of a singe row in your INVOICE AMOUNT table? Hmm, that would be the value for [INVOICE AMOUNT] in that row. The AVERAGE of a single value is the value. Hence, that row's [INVOICE AMOUNT] can never be less than itself and definitely won't be less than itself minus some number and hence why you get NO for everything.
Now, in your case, if you are not worried about 2, then you could get around this by changing your THRESHOLD formula to:
[THRESHOLD] = CALCULATE(AVERAGE ( [INVOICE AMOUNT] ),ALL('Table')) - 12500
or
[THRESHOLD] = AVERAGEX ( ALL('Table'),[INVOICE AMOUNT] ) - 12500
Second formula edited thanks to @CNENFRNL catching my copy and paste error!
@Greg_Deckler I'm afraid there was a slip of pen in
[THRESHOLD] = AVERAGEX ( 'Table',[INVOICE AMOUNT] ) - 12500
instead, I think it's
[THRESHOLD] = AVERAGEX ( ALL ( 'Table' ), [INVOICE AMOUNT] ) - 12500
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL - Yeah, sorry, I was messing around and I copied my Column formula version over. When you are in a calculated column and refer to a table or a column within an aggregation you actually are not in row context. You are in the context of all rows. This is why Column = MAX([Value]) returns the maximum of the entire column (highest row). But, if you use a measure and refer to a column then the row context becomes part of the overall filter context and you return only the value of the current row in your aggregation. Nifty huh? So, this is why it is almost always a bad idea to use measues in calculated columns because they operate very differently than what one probably expects. So, yes, the measure version of that would need the ALL('Table').
Since it was marked as the solution, I'm going to edit it so people that don't read the entire thread don't get confused.
I attached my working copy PBIX below sig, Table (4) if you want to see what I was messing around with.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |