Forum Discussion
Dynamic filtering of table
Hello,
Could you, please, help me with the question of dynamic filtering.
I have table like this:
Item ID Column Duration Project
1 New 10 A
2 New 2 A
3 In Progress 5 B
I created a Measure:
Percentile 90 = CALCULATE(PERCENTILE.INC('PBIs Duration'[Sum Duration],0.9),'PBIs Duration'[Sum Duration]<>0)
I added to the dashboard two filters: Project and Column.
I added a diagram for 'Percentile 90' and can see the result after filtering.
The main question is how to show only items which have Duration > Percentile 90 after filtering ?
I created a new Calculated Table:
PBIs under Percentile 90 = FILTER('PBIs Duration', 'PBIs Duration'[Sum Duration]>PERCENTILE.INC('PBIs Duration'[Sum Duration],0.9))
The additional question: why: PBIs under Percentile 90 = FILTER('PBIs Duration', 'PBIs Duration'[Sum Duration]>[Percentile 90]) doesn't work?
Then I add Item If of the second table to dashboard and applied filters, but Power Bi shows me items which were filtered by Percentile 90 evaluated for the initial table without applying filters by Column and Project.
How to add dynamic filtering for the second table as well?
Thank you!
Hi, I have created filtering by formula and added column with value for every pair of Projects and Columns:
Column 2 = CALCULATE(PERCENTILE.INC('PBIs Duration'[Sum Duration],0.9), FILTER('PBIs Duration', EARLIER('PBIs Duration'[ColumnPK]) = 'PBIs Duration'[ColumnPK] && EARLIER('PBIs Duration'[TeamProject])='PBIs Duration'[TeamProject]))
8 Replies
v-huizhn-msftMicrosoft Employee
Hi Vladislav_1986,
What's the meaning of [Sum Duration] field? It is a calculated column using the sum[Duration]? If It is, the measure [Percentile 90] returns 17, when you create a calculated table:PBIs under Percentile 90 = FILTER('PBIs Duration', 'PBIs Duration'[Sum Duration]>[Percentile 90]) , could you please share more details for further analysis?
Best Regards,
AngeliaHello, many thanks for the reply!
Let's assume [Sum Duration] column like just a column with numbers and [Percentile 90] just any measure based on this column.
The question is how to apply filtering when you click on filters in the dashboard area to the calculations inside formulas for calculated measure/columns/tables.
Steps:
1. I created ([Percentile 90]) measure based on table column ([Sum Duration])
2. I added this measure ([Percentile 90]) to dashboard area, and, for eaxample, the initial value for this measure is 100
3. I added filter ([Project]) to dashboard area, I click on filter - select 'A' and new value for measure ([Percentile 90]) = 50
4. I created new table with filtering in DAX formula based on the ([Percentile 90]) to show only rows where the value less then measure
5. I added rows from new table to dashboard
6. I click on filter ([Project]) on dashboard - select 'A', so ([Percentile 90]) = 50, new table shows me only rows filtered by this 'Project' filter, but filter in the DAX formula still uses value = 100, not filtered new value =50.
So the question is how to connect filters in the dashboard area with DAX formulas.
Thank you!
- v-huizhn-msft
Hi Vladislav_1986,
Please try to add ALLSELECTED in measure [Percentile 90], and check if it works fine.Percentile 90 = CALCULATE(PERCENTILE.INC('PBIs Duration'[Sum Duration],0.9),'PBIs Duration'[Sum Duration]<>0,ALLSELECTED('PBIs Duration'))
Best Regards,
Angelia