Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
@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.
Hi @mohammedismail,
Did you try my formula? It worked in the test file.
Best Regards,
Dale
@v-jiascu-msft I did try.. I guess I'm doing something wrong ? PLease see the screenshot below and advice 🙂
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
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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |