Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Distinct count based on column values

Need a measure to calculate Distinct count of IDs that have both Types - Type1 and Type2   Sample Data:    ID Column2 Column3 Type 1 … … Type1 2 … … Type1...
Need Help
KL718's avatar
KL718
Frequent Visitor
7 years ago

HI,

 

This is not the exact soultion but would help. It will give you output as below. Once you have it split the type & you can write DAx to calculate Distinct.

 

ID

Type
2Type1 Type1 Type2 Type2

 

 

https://www.youtube.com/watch?v=oseoa_2OYqg

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Anonymous 

 

As a MEASURE,,one way could be

 

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[ID] ),
        VAR temp =
            CALCULATETABLE ( VALUES ( Table1[Type] ) )
        RETURN
            CONTAINS ( temp, [Type], "Type1" )
            && CONTAINS ( temp, [Type], "Type2" )
    )
)

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous

     

    Another way could be

     

    Measure 2 =
COUNTROWS (
    FILTER (
        VALUES ( Table1[ID] ),
        COUNTROWS (
            INTERSECT ( { "Type1", "Type2" }, CALCULATETABLE ( VALUES ( Table1[Type] ) ) )
        ) = 2
    )
)