CODEWITHSUNDEEP
powerbidax
StackTrace
StackTrace

Reputation: 9416

DAX query to exclude duplicated rows from table row count

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])

Upvotes: 0

Views: 4528

Answers (1)

WimV
WimV

Reputation: 1005

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

Upvotes: 3

Related Questions