Forum Discussion

Regular Visitor

3 years ago

Solved

Distinctive Count across two columns

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 measu...

Need Help

Anonymous
3 years ago
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.
Distinctive Count across two columns.pbix53 KB

VahidDM

Super User

3 years ago

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