How to get Table to show Calculated Measured values only in PBI?

[AliRade]

Hi Community,

I'm hoping someone can provide assistance in how to get a table to show only selected records (selected records are based on what is choosen on another Matrix).

Basically, I have one master table with all the required data. This table is a visual and lists out everything.

I have some Calculated Measures. These calculated measures are shown in a Matrix Visual. The Calculated Measures are just stats. Now when i click on a particular stat, the table should filter to show the records related to that stat.

I have attached a sample pbix file. https://www.dropbox.com/s/d6acziw2a6ba9k0/Measure_Table_Filter.pbix?dl=0 

The top is the main master table. The bottom is the Matrix Visual (which includes Calculated Measures):

The top is the main master table. The bottom is the Matrix Visual (which includes Calculated Measures)

When I click on Category A1 - Transit Count "2", I want the table to show only the '2' records in Category A1 which Status is Transit, and NOT all A1 records:

When I click on Category A1 - Transit Count "2", I want the table to show only the '2' records in Category A1 which Status is Transit, and NOT all A1 records

Similarly, the table should only show A3 records, which Status is Available:

Similarly, the table should only show A3 records, which Status is Available

Similarly, the table should show A3 records which are 'Critical' and 'Available' only:

Similarly, the table should show A3 records which are 'Critical' and 'Available' only

[v-eqin-msft]

Hi @AliRade ,

Based on this: When I click on Category A1 - Transit Count "2", I want the table to show only the '2' records in Category A1 which Status is Transit, and NOT all A1 records:

You may create a Table visual with Category and Status, so when you click a data point, the Matrix visual could be filtered as you expected:

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

[AliRade]

Thanks Eyelyn.

This is an option which could work on small set of data (like my example data), but it takes up the real estate if applying on large set of data. The table will be huge as theres many different Categories.

Thanks for the suggestion 🙂

[AlexisOlson]

I don't think there's a clean way to do this. The cross-filtering logic doesn't know, for example, that your measure called TransitCount should filter the Status column.

[AliRade]

What would be a 'messy' way of doing this? @AlexisOlson 

[AlexisOlson]

It might be possible to get it working if your columns actually are dimensions rather than measures. You'd need to set up a header table and a switching measure (see this example) for the bottom table and make this new table filter your top table appropriately.

I don't recommend this approach here, so I'm not going to try it to see if I can make it work or not.

[AliRade]

I couldnt get my head around to the switching measure and header table requirment.

I can follow through from the example you shown, but struggled to apply the setup to my set of data 😞

I see why you didn't recommend this approach here!

[AliRade]

Thanks AlexisOlson.

This looks like a very out of the box solution! I will give this switching measure a try and see how it goes.