Multi-row card column filters

[Inna2602]

Hi,

I cannot figure out a way to disconnect the filters I apply to each column in this visual.

A simplified scenario for the sake of explaining what I need: 

I have a list of 100 chemical substances. 

Some are restricted, some are prohibited and some have no regulatory status (Options are R, P, NA). I am working with 4 jurisdictions: Canada, EU, US, and Japan.

What I am trying to do, is to create a multi-row card which shows a count of chemicals under each country that have a P or an R status (i.e. filtering out NAs and summing up the P & R status).

 First I added the EU:  (e.g. count = 20, after adding the Regulatory Status in EU as a column and also dragging it into visual's filters and selecting only P and R).

Next I added the US (e.g. 16 chemicals with either P or R regulatory status). However, the EU count is now altered as well because the US filter is also impacting the EU count. 

I know how to edit interactions between visuals on the dashboard but I can't figure out how to apply a filter to each column without affecting each other's counts...

[v-xinruzhu-msft · Accepted solution]

Hi @Inna2602 

Based on your description, Is your data structure like the following?

If your structure like this, you can try the solution.

You can create a new table named country.

Then create a measure

MEASURE =
SWITCH (
    SELECTEDVALUE ( Country[Country] ),
    "Canada", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Canada] IN { "R", "P" } ),
    "EU", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[EU] IN { "R", "P" } ),
    "Japan", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Japan] IN { "R", "P" } ),
    "US", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[US] IN { "R", "P" } )
)

Then put the country field and measure to the multi-card.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.