Filter table based off another filtered table

[Liaise]

Hi, I was wondering if anyone can help me.

I have two tables that have columns with similar values. In the example below i have TableA & TableB. TableA is the main table containing all possible unique StoreName values. TableB is a table that can contain mutiple StoreName values but doesn't always have every value from TableA.

TableB gets filtered by slicers and what i need is whatever State & Category values are left in TableB to filter TableA State & Category and then return results frm TableA where the StoreName does not exist in TableB

Hope this makes sense

[LivioLanzo · Accepted solution]

try with this measure and then using a filter > 0

Measure =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( TableA[StoreName] ),
        TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
        TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ),
        EXCEPT ( VALUES ( TableA[StoreName] ), VALUES ( TableB[StoreName] ) )
    )
)

[v-lili6-msft · Accepted solution]

HI, @Liaise

     You can try this formula as below:

Measure2 = 
VAR _TABLE2=CALCULATETABLE(VALUES(TableB[StoreName]),ALLEXCEPT(TableB,TableB[Category])) RETURN

VAR A=COUNTROWS (
    CALCULATETABLE (
        FILTER(VALUES ( TableA[StoreName] ),NOT TableA[StoreName] in _TABLE2),
        TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
        TREATAS ( VALUES ( TableB[Category] ), TableA[Category] )
)) RETURN
VAR B=COUNTROWS(TableA) RETURN
IF(ISFILTERED(TableB[Category])&&ISFILTERED(TableB[State]),A,B)

Result:

IF not select from slicer

then I filter them

here is pbix, please try it.

https://www.dropbox.com/s/kuyzsj1j5xkkheg/Filter%20table%20based%20off%20another%20filtered%20table....

Best Regards,

Lin

[Liaise]

Thank you for your help also. Your solution did work but required both filters to have a selection which is not always required in my situation. But still a great solution and appreciate it

[LivioLanzo · Accepted solution]

try with this measure and then using a filter > 0

Measure =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( TableA[StoreName] ),
        TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
        TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ),
        EXCEPT ( VALUES ( TableA[StoreName] ), VALUES ( TableB[StoreName] ) )
    )
)

[Liaise]

Thank you so much for your help. Your solution did work. I had a relationship between the two tables and once i removed it then your DAX solution worked perfectly.

[Liaise]

Hi this actual filters out all results when i try. If i take out the EXCEPT statement line i do get all stores that fall under the two filters which is great. But once i try to find the unmatched using EXCEPT i get no results. 

[LivioLanzo]

ok.. could you post a more complete dataset? what visual are you building? maybe upload a pbix file?