cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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.

Solution Sage

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

Microsoft Employee
Super User

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.

Regular Visitor

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?

Helper I

@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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.