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.

     

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