Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jklassen
Regular Visitor

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

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

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

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 )

Regards
Zubair

Please try my custom visuals

Zubair,

 

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

@jklassen

 

Please see attached file

 

filt.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.