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
s_mansell
Helper I
Helper I

Filtering Out Category

Hi all, 

 

Is there a way I can use and opposite of the 'IN' function to filter a measure? I have previously used a quick measure to filter my value e.g. 

 

Sales filterd out 'B'=
CALCULATE(
    [Sales],
    'Cost Account'[Category] IN {
        BLANK(),
        "A",
        "C",
        "D",
        "E"})
 
This does work however the list of categories will change meaning the value won't be correct if any changes to the list occur.
Instead I am looking to filter out 'B' rather than keep in all other categories e.g.
Sales filterd out 'B'=
CALCULATE(
    [Sales],
    'Cost Account'[Category] OUT {
        BLANK(),
        "B"})
 

How can I write this correctly? 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @s_mansell ,

 

 

You can write this as 

 

Sales filterd out B=
CALCULATE(
    [Sales],
NOT
    'Cost Account'[Category] IN {
        BLANK(),
        "B"})
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi  @s_mansell 

You could use not in logic in dax, but it is this syntax NOT <column/expression> IN {list}

it is like harshnathani's reply.

 

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
harshnathani
Community Champion
Community Champion

Hi @s_mansell ,

 

 

You can write this as 

 

Sales filterd out B=
CALCULATE(
    [Sales],
NOT
    'Cost Account'[Category] IN {
        BLANK(),
        "B"})
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

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.