Distinctive Count across two columns

[fcndsnchz]

Here is an example of what i'm trying to do:

DATE	ID1	ID2
2/3/2023	#1	#2
2/3/2023	#2	#3
2/3/2023	#4	#2
2/3/2023	#4	#1
2/3/2023	#1	#5

I'm looking for dax measue to obtain a distinctive count of the #id that are displayed for each date regardless of whether they are in the "id1" or "id2" column.

The result would be like this:

ID#1 = 1

ID#2 = 1

ID#3 = 1

ID#4 = 1

ID#5 = 1

And the dinstictive count would be equal to 5.

Thanks!

[Anonymous · Accepted solution]

Hi @fcndsnchz 

You can refer to the following example.

Create a new table:

Table = var a=SUMMARIZE('Table (2)',[DATE],'Table (2)'[ID1])
var b=SUMMARIZE('Table (2)','Table (2)'[DATE],'Table (2)'[ID2])
return UNION(a,b)

Then put the column to the table visual, and create a new measure:

Measure 2 = DISTINCTCOUNT('Table'[ID1])

Output:

Best Regards!

Yolo Zhu

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

[ppm1]

The easiest way to do this is to unpivot your ID1 and ID2 columns and then use a measure like this one. Replace T9 with your actual table name.

DC = COUNTROWS(SUMMARIZE(t9, T9[Value], T9[DATE]))

Here is what the unpivoted table looks like.

Pat

[VahidDM]

Hi @fcndsnchz 

You can use the DAX formula COUNTROWS(DISTINCT(UNION(VALUES(OriginalTable[ID1]),VALUES(OriginalTable[ID2])))) to get the distinct count of the #id that are displayed for each date regardless of whether they are in the "id1" or "id2" column.

Here is how it works:

• VALUES(OriginalTable[ID1]) and VALUES(OriginalTable[ID2]) will give you a distinct list of all the values in the ID1 and ID2 columns respectively.
• UNION(VALUES(OriginalTable[ID1]),VALUES(OriginalTable[ID2])) will combine the distinct lists of ID1 and ID2 into one list, and remove any duplicates.
• COUNTROWS(DISTINCT(UNION(VALUES(OriginalTable[ID1]),VALUES(OriginalTable[ID2])))) will give you the count of distinct values in the combined list.

So this formula will give you the distinct count of all unique IDs regardless of which column they appear in.

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[EserB]

Hi VahedDM,

Consider there are diffirent dates in the  Date column. how would I count for each dates and how would I write in a new coumn near each dates?

[SG-01]

@VahidDM Hi, I want to use this solution to get the count of unique values in two columns. For example the combination of Product_ID and Customer_ID is unique and I want to count those values. However for each unique value it is counting it as 2 instead of 1. How can I fix that?