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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Zubair_Muhammad

     

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

     

    Take care,

     

    Kevin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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-