Forum Discussion

Liaise's avatar
Liaise
Frequent Visitor
7 years ago
Solved

Filter table based off another filtered table

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 u...
  • LivioLanzo's avatar
    7 years ago

    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's avatar
    7 years ago

    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.pbix?dl=0

     

    Best Regards,

    Lin