cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atul06 Frequent Visitor
Frequent Visitor

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

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.

3 REPLIES 3
KL718 Frequent Visitor
Frequent Visitor

Re: Distinct count based on column values

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

Super User
Super User

Re: Distinct count based on column values

@atul06 

 

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" )
    )
)

 

 

Super User
Super User

Re: Distinct count based on column values

@atul06

 

Another way could be

 

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