Cross Filter across tables - 2

[pbhat89]

I previously raised this query ( https://community.powerbi.com/t5/Desktop/Cross-filtering-across-tables/m-p/1398390# ) while i selected as accepted solution however it doesn't comepletely resolve the issue 

Restating the example below, with minor edit in filters :

TABLE 1

Each row shows 1 ID only. ID does not repeat in TABLE 1

Measure : Count_of_unique_ID = 8

TABLE 2 

Each row is a unique combination of ID and Condition. i.e. ID,condition won't repeat. The table may have <= IDs from table 1. E.g. it will not have a new ID 10 which has not occured in table 1.  

The current join is in BOTH direction and via ID 

FILTERS & MEASURE - EXPECTED OUTPUT.

FILTER 1 = AGE < 35 

FILTER 2 = EXCLUDE (not filter on) IDs with CONDITION (A OR B) - two selected [ multiple selection should be allowed here in solution]

TABLE 1 after filters 

Measure : Count_of_unique_ID = 3

Based on previous solution by (Community Support Team _ zhenbw) in link (https://community.powerbi.com/t5/Desktop/Cross-filtering-across-tables/m-p/1398390#) 

it only works if one condition is selected i.e. A . If i select two conditions, it reverts to its original count. 

What is the best appraoch to resolve this?

[Anonymous]

Hi @pbhat89 ,

I can't get my head around the last part of your query or what's your expected output needs to look like. 

But I have made simple Power Bi filter which works around in similar way. Let me know if this is what you had expected. 

Else please provide the expected output with selected filters. So will have a clear idea. 

If this answers your question, please give a thumbs up and accept as a solution. 

Thanks

[pbhat89]

No , this isn't the correct output - the filter 2 needs to EXCLUDE ( like a ANTI join ) 

so remove all IDs from table 1 where condition A OR B happened. Keep the rest ( including the ones which never occured in table 2  i.e. 4,5,6,8 )

the output of after FILTER 1 and FILTER 2 is a measure value given  :

Measure : Count_of_unique_ID = 3 ( from the original 8 IDs from the table before without any filters ) 

if you put it in order 

Filter 1 - Age - Removes ID (3,5)

Then applied further (along with above )

Filter 2  - Remove IDs of people who had Condition A Or B ( allow for multiple select , so that i can modify this to jsut A in future or A, B, C )  - This removes IDs (1,2,7)

Remaining IDs = 4,6,8 = 3 IDs count 

your method is not including the IDs which didn't come in table 2 . that isn't expected. Table 1 is the main table and join should be in a like left anti join.