-->

Display Count of Users based on Multiple slicer va

2019-08-31 00:25发布

问题:

I am working on a Viewership table which tells numbers of customers watches asset. There are two asset filters. One for watched and one for not Watched. Based on the asset filters, I need to display number of customers who watched the show & customers who didn't watched the show. Below is the method I have used.

First, created a new table "Asset":

This table contains unique assets and used it to create a slicers that affects DAX measure but does not affect the visual (table). Asset table disconnected from the Viewership table (no relationships).

Created the copy of the above table for not watched assets and named it as asset_1

below is the viewership table.

Created below measures to get the customers who viewed the selected asset (from first slicer) & he customers who not viewed the selected asset (from second slicer) &

Watched 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")


Not Watched Status = 
  VAR Selected_Assets = ALLSELECTED(Asset_1[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")

I got below result

after applying below filters in the visual filter pane I can see the desired result.

Watched Status= "Watched", Not Watched Status = "Not Watched"

Now I got a Requirement to show total number of customers (Instead list of customers) with above filter criteria. For e.g: In the above case customer count is 1.

I have tried below measure.

Customer Count = CALCULATE(DISTINCTCOUNT(Viewership[customer_id]),FILTER(Viewership,([Watched Status]= "Watched") &&left([Not Watched Status],3)="Not"))

The expected result is 1 (Customer ID: 4). But I am getting result as 2. Could someone help to identify the issue here?

回答1:

What you can do is drag teh customer_id in the visual as column and ensure it is set to count distinct.

Select your visual, go the the vusialization panel under values you can see the column, click on arrowdown, popus will show: