Dynamic filtering of table

[Vladislav_1986]

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!

[v-huizhn-msft]

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,
Angelia

[Vladislav_1986]

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,
Angelia

[Vladislav_1986]

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

[Vladislav_1986 · Accepted solution]

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]))

[jimmyswoosh]

Hi @Vladislav_1986,

I'm reading through your issue and seems like I have a similar problem. Can you elaborate on what 'ColumnPK' is and what values are in it?

Thanks!

[Vladislav_1986]

Hello!

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.