Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a table that is grouped by "Page" and with sum and counts of pageviews, signups and signups %.
I would like to filter it to show only rows where pageviews are lower than x or signups higher than x.
I am able to do it with the "Visual Level Filters", but I would like to add a control that users can just drag to adjust the values.
Is this possible?
I have tried to add a slicer, but it only lets me filter the underlying data
Solved! Go to Solution.
Hi @Kimcha,
Perhaps you could try below steps.
Create a calculated table which lists all options you want to allow users to select from a slicer. Later, you should add this column into slicer.
DataTable = DATATABLE("Selection",Integer,{{10},{300},{500}})
Create measures like below:
Measure page viewers = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[unique page viewers] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[unique page viewers] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) ) Measure signups = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[signups] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[signups] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) ) Measure signups% = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[signups %] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[signups %] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) )
Then, add above measures into table visual.
Best regards,
Yuliana Gu
Hi @Kimcha,
Perhaps you could try below steps.
Create a calculated table which lists all options you want to allow users to select from a slicer. Later, you should add this column into slicer.
DataTable = DATATABLE("Selection",Integer,{{10},{300},{500}})
Create measures like below:
Measure page viewers = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[unique page viewers] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[unique page viewers] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) ) Measure signups = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[signups] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[signups] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) ) Measure signups% = IF ( ISFILTERED ( 'DataTable'[Selection] ), CALCULATE ( SUM ( 'Page table'[signups %] ), FILTER ( 'Page table', SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] ) ) ), CALCULATE ( SUM ( 'Page table'[signups %] ), FILTER ( 'Page table', 'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] ) ) ) )
Then, add above measures into table visual.
Best regards,
Yuliana Gu
@v-yulgu-msft, I have a question.
In the second part of each measure calculation(the "else" part of isfiltered), isn't it simpler to just use SUM?
I tried it myself and it works well.
For example, instead of:
Measure page viewers = IF(ISFILTERED('DataTable'[Selection]),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table',SUM('Page table'[unique page viewers])<=MAX('DataTable'[Selection]))),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table','Page table'[unique page viewers]<=MAX('Page table'[unique page viewers]))))
just use:
Measure page viewers2 = IF(ISFILTERED('DataTable'[Selection]),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table',SUM('Page table'[unique page viewers])<=MAX('DataTable'[Selection]))),SUM('Page table'[unique page viewers]))
Thanks!
Wow, thank you so much @v-yulgu-msft!
I am very new to PowerBi and this is a little over my head. Would it be possibel for you to please upload the file you used to come up with this, so that I can play around with it and figure out how all the pieces come together?
I really appreciate your help!
Hi @Kimcha,
I have uploaded my pbix file for your reference.
Regards,
Yuliana Gu
Awesome, this is working well! Thank you very much!
I have a table that is grouped by "Page" and with sum and counts of pageviews, signups and signups %.
I would like to filter it to show only rows where pageviews are lower than x or signups higher than x.
I am able to do it with the "Visual Level Filters", but I would like to add a control that users can just drag to adjust the values.
Is this possible?
I have tried to add a slicer, but it only lets me filter the underlying data
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
106 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |