How to create an interactive table in report which counts rows of another table by group and filter

[alku0530]

Dear all,

I am new to power bi(<1 week), but have tried any possible solution in the past few days with no progress. Really need your help.

I am tasked to create an interactive report using power bi, which contain two data sets. Table 1 has id and percentile, table 2 contains id and risky object with risk levels (and the risk events are recorded as id2). The goal is to create a slicer for percentile and a parameter for costs to solve risk object, and with the slicer and parameter chosen, an interactive table will show the number of risky objects by risk level and automatically calculate corresponding total costs.

The example data is here:

table 1
id       percentile

AAA	93
BBB	87
CCC	84
DDD	80
EEE	75
FFF	60
GGG	58
HHH	40
III	32
JJJ	17

 table 2

id      risk level     id2

AAA	High	1
AAA	Low	2
BBB	High	3
BBB	Medium	4
BBB	Medium	5
BBB	Medium	6
CCC	Low	7
CCC	Low	8
DDD	High	9
DDD	Low	10

So far I am able to create a count by type table using the following dax code:

Spoiler

count by type = SUMMARIZECOLUMNS('Table (2)'[risk level], "risks", COUNTROWS('Table (2)'))

And the current result is as attached:

Ideally as the user move the slicer, the responding count of risks events by type should change accordingly. Although the slicer interact with the table2, it is unable to refresh the risks in 'count by type' table. I know I should replace the column to a measure, or somwhow link the 'count by type' table to the slicer, but just cannot figure how to do them.

Any help is appreciated!

[alku0530]

Hi @gmsamborn 

Thank you for your reply and advice. However, it must due to my lack of experience. I still don't see how you could create a interactive summary table for table(2). Would you mind to explain more?

[alku0530]

Hi @gmsamborn 

Eventually I kind of change my route and get my result. Thank you anyway. I will also explain my solution later when have time.

[gmsamborn]

Hi @alku0530 

I took a look at your model and made a couple of small changes.

Usually I would leave a parameter table like Percentile as a disconnected table.  If your Percentile column in 'Table (1)' is always a whole number between 0 and 100, you can create a one-to-many relationship between 'Percentile'[Percentile] and 'Table (1)'[Percentile] and filtering will handle everything else.

(With a disconnected table, you would need one or more [Include] measures to filter table visuals.  Filtering 'Table (1)' is no problem but 'Table (2)' is tougher.)

Let me know if you have any questions.

Interactive tables - 2.pbix

Also, I'm not sure what your Cost slicer is for.