Forum Discussion

fcndsnchz's avatar
fcndsnchz
Regular Visitor
3 years ago
Solved

Distinctive Count across two columns

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

 

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

 

Need Help
  • Anonymous's avatar
    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.

     

5 Replies

  • VahidDM's avatar
    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!! 

    LinkedIn | Twitter | Blog | YouTube 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

    • EserB's avatar
      EserB
      Regular Visitor
      2 years ago

      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?

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.