Forum Discussion

Anonymous's avatar
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 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?

Need Help
  • Zubair_Muhammad's avatar
    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 )
    )

7 Replies

  • Zubair_Muhammad's avatar
    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's avatar
      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's avatar
        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

    • dax_powerbi's avatar
      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)
      )