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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
OCBB_SFAFPandA
Resolver I
Resolver I

DAX measure to exclude certain categories to create a new table measure

Hello,

 

I am looking to create a new measure to include in a table. Say I  have 30 categories, and I need to exclude 10 of them on a measure, because this # will be used in multiple visuals, I don't want to exclude in the filter pane of each visual.

 

I've only seen examples where And/Or are used and those only have 2 logical areas.

 

Trying something like

 

   CALCULATE(
    SUM(FACTS_Service_Per_Category[Price]) ,
   AND( FACTS_Service_Per_Category[Category] <> "Product 1",
        FACTS_Service_Per_Category[Category] <> "Product 2",
        FACTS_Service_Per_Category[Category] <> "Product 3",
        FACTS_Service_Per_Category[Category] <> "Product 4",
        FACTS_Service_Per_Category[Category] <> "Product 5",
   )
1 ACCEPTED SOLUTION
WinterMist
Impactful Individual
Impactful Individual

@OCBB_SFAFPandA 

 

Is this what you are looking for?

 

Measure NOT IN =
CALCULATE( 

   SUM(Facts[Price]),
   FILTER(
      Facts,

      NOT (Facts[Price]) IN {"P1", P2", "P3", "P4", P5"}
   )

)

 

Regards,

Nathan

View solution in original post

4 REPLIES 4
WinterMist
Impactful Individual
Impactful Individual

No problem.  Glad to help!

WinterMist
Impactful Individual
Impactful Individual

@OCBB_SFAFPandA 

 

Is this what you are looking for?

 

Measure NOT IN =
CALCULATE( 

   SUM(Facts[Price]),
   FILTER(
      Facts,

      NOT (Facts[Price]) IN {"P1", P2", "P3", "P4", P5"}
   )

)

 

Regards,

Nathan

Yes, thanks!

OCBB_SFAFPandA
Resolver I
Resolver I

Guess I have it with nested ANDS. Is there a better way to write this? 

 

CALCULATE (
    Sum (
        FACTS_Service_Per_Category[Price]),
        AND ( FACTS_Service_Per_Category[Category] <> "Product 1",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 2",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 3",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 4",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 5",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 6",
        AND ( FACTS_Service_Per_Category[Category] <> "Product 7",  FACTS_Service_Per_Category[Category] <> "Product 8"
        
        )))))))
)

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors