Filter Table Data based on Dynamic Measure Value

[mohammedismail]

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.

[noumanqaiser]

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.

[v-jiascu-msft]

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]

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

[v-jiascu-msft]

Hi @mohammedismail,

Did you try my formula? It worked in the test file.

Best Regards,

Dale

[mohammedismail]

@v-jiascu-msft I did try.. I guess I'm doing something wrong ? PLease see the screenshot below and advice 🙂

[v-jiascu-msft · Accepted solution]

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

[mohammedismail]

@v-jiascu-msft Thank you So much buddy. You made my day ! 🙂

[Anonymous]

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]

@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]

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