Distincount based on multiple filters in one column

[Anonymous]

Hi all,

Im have one table with two columns, one with the ID and one with text. I want to distincount the ID table but only based if the two filtering critiera is met. For example in the table below, I want to count numbers of ID that contains both "FAST" and "MID" in the Type column (ID nr 1 and 3).

My starting code was: 

ID = CALCULATE(DISTINCTCOUNT('Export Data SE'[ID]),FILTER('Export Data SE','Export Data SE'[Type] in {"FAST","MID"})

I assume this is incorret: in "{"FAST","MID"}" and I have tried several other ways e.g. in "FAST" &&"MID", "FAST" || "MID".

Thanks in advance!

ID	Type
1	FAST
1	MID
1	SLOW
2	SLOW
3	MID
3	FAST
4	FAST

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You can create a measure with below code:-

Measure = 
CALCULATE (
    DISTINCTCOUNT ('Export Data SE'[ID]),
    CALCULATETABLE (
        VALUES ( 'Export Data SE'[ID] ),
        'Export Data SE'[Type] = "FAST"
    ),
    CALCULATETABLE (
        VALUES ( 'Export Data SE'[ID] ),
        'Export Data SE'[Type] = "MID"
    )
)

Output:-

Thanks,

Samarth