Filtering rows by content of selected column, but with an exception list.

[jklassen]

Hello,

I'm sure there is a rather simple answer to this need, but I don't know what it is. (I fear I'm misisng the obvious.) I'm not very familiar with DAX yet either. If anyone can guide me a bit that'd be great.

I have a table in a Power BI dataset with a couple thousand rows. Some of the columns are like the following example.

ID     Language  Type
12345  eng       3
67890  fra       1
54321  spa       1
09876  cmn       2
abc12  eng       2
34def  cmn       1
56fed  por       2

I require a filtered result containing rows where Languages are a Type 3 or 2, but where the same Language is NEVER found in the table with Type 1. In this example I would want [eng, por]. I would not want cmn.

Thanks for any help.

Jeff

[Zubair_Muhammad · Accepted solution]

HI @jklassen

Please try this calculated Table

From the Modelling Tab>>>NEW TABLE

Filtered_Table =
VAR temp1 =
    CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] IN { 2, 3 } )
VAR temp2 =
    CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] = 1 )
RETURN
    EXCEPT ( temp1, temp2 )

[jklassen]

Zubair,

Thank you, for an accurate and quick reply. I have adapated your solution for my data and it's working as needed.

[Zubair_Muhammad]

@jklassen

Please see attached file