Can I summarise a table dynamically based on the user's filter selection?

[D_PBI]

Hi all,

It's late at night for me so please excuse me if this question seems rushed.

I have the below table:


I have created a simple table (via Enter Data) to hold Case Manager and Case Associate values:


I would like to count the unique rows in the ID column where the IP column has 'U' (it will always be 'U' that I'm interested), grouped by the Manager if the user chooses 'Case Manager', or by Associate if the user choose 'Case Associate'.

So if the user chooses 'Case Manager' the results table would be:
Mike 2
Jane 2

If the user choose 'Case Associate' then results table would be:
Jon 3
Jane 1

How do I ahcieve this?
Thanks.

[Anonymous]

HI @D_PBI,

AFAIK, current power bi does not support creating dynamic calculated column/table based on filter selections. They are host on different data levels and you can't use child level to affect its parent.

For this scenario, I'd like to suggest you enter to query editor and create a parameter and invoke it in the query table filter.  Then you can change the parameter to affect the table records and it will indirectly change the calculated table result if you referenced to the filtered table that applied the query filters.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[D_PBI]

I'm attempting to do the same this way but without luck due to the error.

Am I close to acheiving my aim or am I completing off target?

[D_PBI]

@FrankAT  - is it possible to do something like the below (example is to explain my aim only)? 
Excuse the red warning indication, the names I've used in the below are to match my example in the first post (which was created for simplicity).

[FrankAT]

Hi @D_PBI ,

if I understood you right than it's not possible, because column manager and associates are in your case filter coordinates which can not be changed on the fly with a slicer in one table visual. You need 2 table visuals or 2 calculated tables to get a result (see figure):

Count Capacity = CALCULATE(COUNTROWS('Table'), 'Table'[IP] = "U")

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Migasuke]

Hi @D_PBI ,

If I get right, the problem should be solved with measure calculate,something like this:

CALCULATE(DISTCOUNT(Table [ID]),Table [IP] = "U")

Let me know if it helped!

[D_PBI]

@Migasuke - I don't believe your suggestion works but thanks for the effort.

[Migasuke]

Hi Again,
You are probably right. Now when I am reading the post again I see I understood it differently.
I think I have an idea how to achieve what you asked. In cas you will not be successfull of previous solutions, let me know and I will try mine.