Distinct count based on column values

[Anonymous]

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
2	…	…	Type1
2	…	…	Type2
1	…	…	Type1
3	…	…	Type2
2	…	…	Type2
1	…	…	Type1
4	…	…	Type2
4	…	…	Type1
4	…	…	Type2

ID 2 and 4 have both types. so expected result is 2.

[KL718]

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
2	Type1 Type1 Type2 Type2

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

[Zubair_Muhammad]

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

@Anonymous

Another way could be

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