Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I am trying to use the dynamic output of a measure as a filter in a DAX formula.
I have a funtion that calculates a ranking score based on user input, e.g. when they select a location the measure produces a score between 1 and 100. I then want to compare this score to an exisitng list of scores.
Using the measure in the formula is producing incorrect results, the user input does alter the output just incorrectly. I am expecting 66 sites to have a greater ranking score.
If I hard code the ranking score I get the expected outcome. Hardcoding Ranking Score as 44 also works.
For reference this is the calculation I am using
Can anybody help me understand how to fix this issue?
Solved! Go to Solution.
@Anonymous
Try this
Greater Count (Measure) = VAR myranking = [Ranking Score] RETURN CALCULATE ( COUNT ( predicted[global_site_id] ), FILTER ( predicted, predicted[Score 0_100] > myranking ) )
@Anonymous
Try this
Greater Count (Measure) = VAR myranking = [Ranking Score] RETURN CALCULATE ( COUNT ( predicted[global_site_id] ), FILTER ( predicted, predicted[Score 0_100] > myranking ) )
I am really struggling with this 😞
How can i get this filter to work in a Measure:
@Zubair_Muhammad Thank you, that works perfectly!
Can you tell me why this approach works but what I had attempted does not?
Hi @Anonymous
Measures are dynamic....Inside an ITERATOR like FILTER/SUMX etc they behave according to the context of the (temporary) Table used as FirstArgument of these iterators.....
In your case you want the MEASURE value to be based on the CONTEXT provided by your Pivot Table or Table or Matrix Visualization (NOT the TABLE created by FILTER function).....so you have to take the Measure out of the Filter Function.....
So this is what we did...First Stored the Value of MEASURE using a VARiable and then used this VARiable inside FILTER function
Thanks @Zubair_Muhammad that has been very helpful, couldn't figure out why my formula wasn't working as intended.
aaargh!
Thanks for your solution!
I had the problem that, my first measure [ACT], done with Sumx, was not considered by my second measure in which i had Sumx and Filter (or even calculate as a try). So a problem of context transition. By the way, this article helped me too: https://blog.enterprisedna.co/2016/08/03/what-is-context-transition-and-why-does-it-matter/
The solution then for me was to call the first measure via a variable (var vACT = [ACT]
Br, G-
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |