Forum Discussion

mohammedismail's avatar
mohammedismail
Helper I
8 years ago
Solved

Filter Table Data based on Dynamic Measure Value

Hi,

 

I have a Dynamic Measure which is created from the What If Parameter

 

The below is my Measure, where Parameter value is %

 

Measure = DISTINCTCOUNT([Supplier_Name])*Parameter[Parameter Value]

 

So I get a Value lets Say 300 from the measure.

 

I have a transaction table Called 'SPEND' where I have a column 'RANK' and the values in this column are ranging from 1 to 5000

 

I want to Filter the Data in the SPEND table based on the Measure..If my measure if 300, I only want the RANK column to show me the 1 to 300 values.

 

Please can someone help.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi mohammedismail,

     

    1. It's [PercentValue] rather than [Percentage Value] in the formula.

    Measure = IF([Rank]<=CALCULATE([PercentValue], all(Sheet1)),1,BLANK())

    2. The Visual Level Filter should be "is not blank".

     

    Best Regards,

    Dale

10 Replies

  • noumanqaiser's avatar
    noumanqaiser
    Advocate I
    7 years ago

    Hello,

     

    Any of the solutions provided here donot work. I am not sure why this is marked as resolved. As far as I understand, dynamic values of measures that change on the front end due to user applying filters o the UI do not affect calculated tables/columns in any way. I tried using all methods specified here but it seems calculated Tables/Calculated columns are directly evaluated based on base context in the underlying data.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    Create a calculated column with the below DAX

     

    Column = IF(Table1[Rank]<=[Measure],1,0) and then pull this column to your visual and filter values 1.

     

    Thanks

    Raj

    • mohammedismail's avatar
      mohammedismail
      Helper I
      8 years ago

      Anonymous Thanks for your response..The Calculated Column is not getting refreshed when the measure is updated.

       

      Please take a look at the dummy file that I created..I want to show only those ranks which are <= Percent value which is Dynamic based in the slider above.

       

      Test.PBIX

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        ok, got it.

         

        Create the measure and then use this in visual filter , filter for value 1 to get the result.

         

        Measure =  IF(MAX(Table1[Rank])<=[Measure],1,0)

         

        Thanks

        Raj

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi mohammedismail,

     

    Seems [Rank] is a measure. Try this formula, please.

    Measure =
IF ( [Rank] <= CALCULATE ( [PercentValue], ALL ( Sheet1 ) ), 1, BLANK () )

    Filter out the blanks in the Visual Level Filter.

     

    Best Regards,

    Dale

    • mohammedismail's avatar
      mohammedismail
      Helper I
      8 years ago

      v-jiascu-msft Anonymous For some reason the Measure value is not being considered in the formula... If I remove the Measure 'Percent Value' and key in a Number it is working fine.. But when I enter the measure it is not working..

       

      Appreciate your help.