cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohammedismail
Helper I
Helper I

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.

1 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
noumanqaiser
Frequent Visitor

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
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft @rajendran 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.

Hi @mohammedismail,

 

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

Filter_Table_Data_based_on_Dynamic_Measure_Value

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

Test.png

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

rajendran
Community Champion
Community Champion

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

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

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

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!