DAX multiple table filters intersection

[cosminc]

Hi all,

i have a base like below and need to create a measure with multiple filters intersection like this:

VAR

_first_filter all ID where Label = A and Value = 1

_second_filter all ID where Label = B and Value = 1

_third_filter all ID where Label = C and Value = 1

RESULT

and need to countrows of source from below with filters Label = C &&  all common ID between the 3 filter tables from above

Thanks in advance,

Cosmin

ID	Label	Value	Client
1	A	1	cosmin
1	B	1	cosmin
1	C	1	cosmin
2	A	0	cosmin
2	B	1	cosmin
2	C	1	cosmin
3	A	0	cosmin
3	B	1	cosmin
3	C	0	cosmin
4	A	1	cosmin
4	B	1	cosmin
4	C	0	cosmin
5	A	1	cosmin
5	B	0	cosmin
5	C	999	cosmin
1	A	1	marius
1	B	0	marius
1	C	1	marius
2	A	0	marius
2	B	1	marius
2	C	1	marius
3	A	0	marius
3	B	1	marius
3	C	0	marius
4	A	1	marius
4	B	0	marius
4	C	1	marius
5	A	0	marius
5	B	1	marius
5	C	0	marius

[v-yulgu-msft · Accepted solution]

Hi @cosminc ,

Please refer to below measure:

Count_rows =
VAR temp_tab =
    ADDCOLUMNS (
        Sample4,
        "Label number", CALCULATE (
            DISTINCTCOUNT ( Sample4[Label] ),
            ALLEXCEPT ( Sample4, Sample4[ID] ),
            Sample4[Value] = 1
        )
    )
RETURN
    COUNTROWS ( FILTER ( temp_tab, [Label number] = 3 && [Label] = "C" ) )

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @cosminc ,

Please refer to below measure:

Count_rows =
VAR temp_tab =
    ADDCOLUMNS (
        Sample4,
        "Label number", CALCULATE (
            DISTINCTCOUNT ( Sample4[Label] ),
            ALLEXCEPT ( Sample4, Sample4[ID] ),
            Sample4[Value] = 1
        )
    )
RETURN
    COUNTROWS ( FILTER ( temp_tab, [Label number] = 3 && [Label] = "C" ) )

Best regards,

Yuliana Gu

[cosminc · Accepted solution]

Perfect!

Thanks,

Cosmin