Display Matched and Non Matched Values based on a slicer value Power BI

Question

I am working on a Viewership table which tells which customer watches which asset. Based on the asset filter, I need to display the customers who watched the show & customers who didn't watched the show. below is my example table

If the asset_id selected as 1 in the slicer, the desired output will be as below

I have tried creating a cross-join table with asset_id and customer_id , but that approach taking much time with large data. Request the experts here to suggest the best optimal solution to achieve this.

RADO RADO · Accepted Answer · 2019-07-07T22:13:07

First, create a new table "Asset":

This table contains unique assets, and we will use it to create a slicer that affects DAX measure but does not affect the visual (table). To achieve that, the Asset table must be disconnected from the Viewership table (no relationships).

In your viewership table, I just renamed "asset" to "asset_id", to be consistent:

Next, create a measure:

Status = 
  VAR Selected_Asset = SELECTEDVALUE(Asset[asset_id])
  VAR Customer_Asset = SELECTEDVALUE(Viewership[asset_id])
RETURN
IF(Customer_Asset = Selected_Asset, "Watched", "Not Watched")

Result:

Slicer here is created from the "Asset" table, and table is a table visual with customer_id and asset_id from the Viewership table (set them as "don't summarize" values). I turned off "total", assuming you don't need it.

This design requires to set Asset slicer to "single selection" mode, to make sure that you are only getting one value from it. If you want the model to work with multi-select slicer, change DAX measure as follows:

Multi Status = 
  VAR Selected_Assets = ALLSELECTED(Asset[asset_id])
  VAR Customer_Asset = SELECTEDVALUE(Viewership[asset_id])
RETURN
IF(Customer_Asset IN Selected_Assets, "Watched", "Not Watched")

Result:

Edit:

To make it work at the customer level:

Customer Status = 
  VAR Selected_Assets = ALLSELECTED(Asset[asset_id])
  VAR Customer_Assets = VALUES(Viewership[asset_id])
  VAR Assets_Watched = COUNTROWS(INTERSECT(Customer_Assets, Selected_Assets))
RETURN
IF(Assets_Watched > 0, "Watched", "Not Watched")

Result:

Explanation: store selected assets in a table variable. Then, store assets visible per customer in another table variable. Find an intersect of the two tables (what they have in common), and count intersect rows. If none - not watched, otherwise watched. If you want, you can actually display the number of movies watched (just return "Assets_Watched" instead of IF statement).

Display Matched and Non Matched Values based on a slicer value Power BI

1 Answers