How to count in Power BI without duplicates by several conditions?

[Anonymous]

Hello, Power BI WiZards!

I broke my mind to make a report that can count by several conditions exclude duplicates. 

In my case i have data with date and other information. And i need to check by condition and count unique condition no metter in which date it happens.

My results i need to have by date. I could make something like this with distinctcount, but it count well only Grand total not the result by day. I need to count duplicate only in first date when it happen and never count in another days.

In this case i count by "passed" and also check other columns for duplicate.

I hope u could help me... I waste like 5 hours to find a solution but i couldnt. I hope you could understand me well.

[Anonymous]

TY, but it doesnt fit in my case... =(
Let me show you on real example. In this real case i have 2 dates, where data for May 22 contain data for 22 and 23 but with changed date to 22. In total i want to see this 15046on 22 and 0 on 23, coz we already count this for 22 and 23 it is all duplicates. I create this example especially to find a way discount this duplicates and count only unique examples...

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to create a virtual table in your measure to calculate the result.

My Sample:

Measure:

Measure = 
VAR _BASIC = ADDCOLUMNS(FILTER('Table','Table'[Approved]<>BLANK()&&'Table'[Status]<>BLANK()),"Earliest",CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[State],'Table'[Approved],'Table'[Status])))
VAR _FILTER = FILTER(_BASIC,[Date] = [Earliest])
RETURN
COUNTX(_FILTER,[State])+0

Result is as below.

Best Regards,
Rico Zhou

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

[jcalheir]

Hi,

Not sure if i understood fully your problem, but if you want to count distinct values by multiple indexes, you can do the following:

Create a new column concatenating the index values you want to check for distincts, ex:

DistinctID = Table[Aproved] &"-"& Table[Status] 

This should give you a new column with the following format "passed-Complete"

Then you can create a DAX formula with distinctcount on this new column.

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[mh2587]

Counting(Measure) = CALCULATE(COUNT(TableName[Date]),TableName[Approved]="Passed")