Query between 2 Tables.

[NithinBN]

Hello All, 

I have2 tables like this, 

I want to list the distinct count of ID, when its, Col-A = 1 and (corrosponding Col-B values in other table is not H),

Here in this example i want to count 103,104,106 and 104 and return 4 as value (rejecting 100, 101, 105 as 500 and 508 has value H )

[tackytechtom · Accepted solution]

Hi @NithinBN ,

I'd probably start with a calculated column in the first table:

ColLvlLookup = 
CALCULATE ( MAX(Table2[lvl]), FILTER ( Table2, Table1[col-B] = Table2[Col-B] ) ) 

And then you can add a measure:

Measure = 
VAR _helpTable =
SUMMARIZE ( 
    FILTER ( Table1, Table1[ColLvlLookup] = "H" ),
    Table1[ID]
)
RETURN
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), Table1[col-A] = 1, NOT Table1[ID] IN (_helpTable) )

You could also write the two code bits together in one measure as well:

Measure 2 = 
VAR _helpTable =
SUMMARIZE ( 
    FILTER ( Table1, CALCULATE ( MAX(Table2[lvl]), FILTER ( Table2, Table1[col-B] = Table2[Col-B] ) )  = "H" ),
    Table1[ID]
)
RETURN
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), Table1[col-A] = 1, NOT Table1[ID] IN (_helpTable) )

Hope this helps! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/