DAX query to exclude duplicated rows from table row count

Question

I have a table with columns "RetailId & State". "State" Column is a measure. In this table column "RetailId" can be duplicated.

So what i want to do is count rows where 'State' equal to 'Success' but also where "RetailId" exists only once (it is not duplicated).

In the row count, i want to ignore rows where "RetailId" exists more than once (is duplicated).

How can i apply the above condition to the DAX query i have below?

COUNTROWS(FILTER(FactProductHistory,FactProductHistory[State] = "Success"))

Sample data below

RetailId | State
------------------
 3       |Success
------------------
 5       |Success
------------------
 3       |Success
------------------
 4       |Success
------------------
 5       |Success
------------------
 1       |Success

In the sample data above, the count would be 2 (row 4 & 6) because "RetailId" for those rows is not duplicated. "RetailId" values 5 & 3 are duplicated so i don't want to include those rows in the count

And below is the DAX expression for the "State" measure column.

State = RELATED(TaskDetail[State])

Can you post some sample data.And what measure you are using the "state" column. The function required will be distinctcount msdn.microsoft.com/en-us/library/gg492142.aspx — WimV

WimV WimV · Accepted Answer · 2017-12-06T13:17:32

I would first create a measure which calculates how many times a RetailID with state "success" occurs

cntRetail:=CALCULATE(COUNT([RetailID]),FactProductHistory[State]="Success")

Then I would use this in a filter, to only select the ones with a count of 1

myMeasure:=CALCULATE(DISTINCTCOUNT(FactProductHistory[RetailID])
,FILTER(all(FactProductHistory[RetailID]),[cntRetail]=1))

DAX query to exclude duplicated rows from table row count

1 Answers