Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using a Measure as a filter in DAX formula

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.
Measure.PNG

 

If I hard code the ranking score I get the expected outcome. Hardcoding Ranking Score as 44 also works.

 

Hard Coded.PNG

 

For reference this is the calculation I am using

 

Score.PNG

 

Can anybody help me understand how to fix this issue?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this

 

Greater Count (Measure) =
VAR myranking = [Ranking Score]
RETURN
    CALCULATE (
        COUNT ( predicted[global_site_id] ),
        FILTER ( predicted, predicted[Score 0_100] > myranking )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this

 

Greater Count (Measure) =
VAR myranking = [Ranking Score]
RETURN
    CALCULATE (
        COUNT ( predicted[global_site_id] ),
        FILTER ( predicted, predicted[Score 0_100] > myranking )
    )

Regards
Zubair

Please try my custom visuals

I am really struggling with this 😞
How can i get this filter to work in a Measure:

sliceGeneric =
VAR m = "BDODEX\AE"
VAR x = "BDODEX\PERF-AE"

RETURN CALCULATE(
    FILTER(systemUsers_A, LEFT(systemUsers_A[DomainName], 9) = m || LEFT(systemUsers_A[DomainName], 14) = x)
)
Anonymous
Not applicable

@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


Regards
Zubair

Please try my custom visuals

Thanks @Zubair_Muhammad that has been very helpful, couldn't figure out why my formula wasn't working as intended.

Anonymous
Not applicable

@Zubair_Muhammad

 

That makes perfect sense, thanks for taking the time to explain.

 

Take care,

 

Kevin

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-

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.