topic Create Measure With Filter Conditions in DAX Commands and Tips

Create Measure With Filter Conditions

rbowen — Wed, 12 Jun 2024 18:03:07 GMT

I'm trying to create a measure that uses some conditions for the final result that will included in a table visual. I have 3 previously created measures, one for Sales, one for Costs and one for Rebates. I also have a column in the data for various categories - A, B, C and D. I need to create a measure that calculates net margin for each of the categories, but when the category is A, I need to include rebates, otherwise just use Sales and Costs. So, something like this:

NetMargin = IF([Category] = "A", [TotalSales] - [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts])

Where I'm running into trouble is the IF([Category] = "A" piece. I get a red underline under the [Category] part with an error message saying "Cannot find name [Category]". I'm positive I have the syntax wrong but not sure how to correct it. Any ideas what I'm missing?

Thank you.

Re: Create Measure With Filter Conditions

Kaviraj11 — Wed, 12 Jun 2024 19:30:44 GMT

Hi,

Try this:

IF(

SELECTEDVALUE([Category]) = "A", [TotalSales] - [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts])

Re: Create Measure With Filter Conditions

rbowen — Wed, 12 Jun 2024 19:56:29 GMT

Thanks Kaviraj11, but this doesn't work either. I still get the same red underline under [Category] with the same error message. I've also tried using the aggregator SUM but same problem. Seems to be related to using a column name with the IF statement in combination with the measures. The syntax is almost certainly incorrect but I'm not sure what the correct syntax should be.

Re: Create Measure With Filter Conditions

Anonymous — Thu, 13 Jun 2024 02:55:20 GMT

Hi rbowen

Kaviraj11 Thank you very much for your prompt reply. Allow me to share some content here.

Measure = IF( SELECTEDVALUE('Table'[Category]) = "A", // YourTableName[Category] = "A", [TotalSales] - [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts] )

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create Measure With Filter Conditions

rbowen — Fri, 14 Jun 2024 18:24:56 GMT

Thank you for the reply Nono. I gave up on this approach and went a different direction by restructuring some of the data and altering some of the relationships.

Re: Create Measure With Filter Conditions

Anonymous — Tue, 25 Jun 2024 07:29:17 GMT

Hi rbowen

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen