Forum Discussion
Dynamic filtering of table
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]))
Microsoft EmployeeHi 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,
Angelia
Hello, 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,
AngeliaHello!
Thank you. I have just tried - still the same behaviour. The new table is filtered by the initial value of masure, filtering on dashboard doesn't affect the formula.
Hello, as I didn't found the way how to connect filter from dashboard, I decided to do pre-filtering in formula in calculated column. One more complex step, but it works.
Thank you.
- v-huizhn-msft
Hi Vladislav_1986,
Congratulations, please mark your workaround as answer, so more people will benefit from here.
Best Regards,
Angelia