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:   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'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.

     

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