Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

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 loc...

Zubair_Muhammad

8 years ago

Anonymous

Try this

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

Zubair_Muhammad

Community Champion

8 years ago

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
Not applicable
8 years ago
Zubair_Muhammad Thank you, that works perfectly!

Can you tell me why this approach works but what I had attempted does not?
- Zubair_Muhammad
  Community Champion
  8 years ago
  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
  - Veles
    Advocate V
    7 years ago
    Thanks Zubair_Muhammad that has been very helpful, couldn't figure out why my formula wasn't working as intended.
dax_powerbi
Frequent Visitor
3 years ago
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)
)