DAX with 2 Table Filters and result the common ID

[cosminc]

Hi all,

I'm stucked with this:

Year	Month	ID	Label	Value	Client
2018	1	1	A	1	cosmin
2018	1	1	B	1	cosmin
2018	1	1	C	1	cosmin
2018	1	2	A	0	cosmin
2018	1	2	B	1	cosmin
2018	1	2	C	1	cosmin
2018	1	3	A	0	cosmin
2018	1	3	B	1	cosmin
2018	1	3	C	0	cosmin
2018	1	4	A	1	cosmin
2018	1	4	B	1	cosmin
2018	1	4	C	0	cosmin
2018	2	5	A	1	cosmin
2018	2	5	B	0	cosmin
2018	2	5	C	99	cosmin
2018	2	1	A	1	marius
2018	2	1	B	0	marius
2018	2	1	C	1	marius
2018	2	2	A	0	marius
2018	2	2	B	1	marius
2018	2	2	C	1	marius
2018	2	3	A	0	marius
2018	3	3	B	1	marius
2018	3	3	C	0	marius
2018	3	4	A	1	marius
2018	3	4	B	0	marius
2018	3	4	C	1	marius
2018	3	5	A	0	marius
2018	3	5	B	1	marius
2018	3	5	C	0	marius

i need to obtain 2 ID list on tables variable and after that i need to count how many are common

when apply slicers on year, month, client i need to adjust after them

something like this:

count_measure = 

VAR tabel_filter_A =

CALCULATETABLE( VALUES( Source[ID] ),FILTER(Source, Source[Value] = 1 && Source[Label] = "A"))

VAR tabel_filter_B =

CALCULATETABLE( VALUES( Source[ID] ),FILTER(Source, Source[Value] = 1 && Source[Label] = "B"))

VAR result = CALCULATE ( COUNT (Source[Value]) , FILTER(Source, Source[ID] IN table_filter_A && Source[ID] IN table_filter_B))

RETURN

result

can you help me please with the right syntax?

Cosmin

[MitchM · Accepted solution]

I would be happy to give it a go. It would help to know what you would expect the result to be for the dataset you provided. It looks like you want to count the value rows when the Source ID is in your two variable tables. In the code you show that would mean that you would count the number of rows where the value is 1 and the source is A AND B. In your data set the ID's 1,4,5 appear in both tables, therefore there should be 18 value rows that meet your criteria. This code will return 18:

Measure = 
VAR Table_A = 
    CALCULATETABLE( 
        VALUES( Source[ID] ),
        Source[Value] = 1,
        Source[Label] = "A" )
VAR Table_B = 
    CALCULATETABLE(
        VALUES( Source[ID] ),
        Source[Value] = 1,
        Source[Label] = "B" )
RETURN
    CALCULATE(
        COUNT( Source[Value] ),
        Source[ID] IN Table_A &&
        Source[ID] IN Table_B
    )

Hopefully this helps.

[cosminc]

that's it!

so 1, 4 & 5 are common ID

the expected result is 3

my mistake that in the example for each label the id is not unique; in the real base it is and your syntax i divided to the number of table variable (/2 in example)

perfect!

thanks!!

Cosmin