The MAX function only accepts a column reference as an argument

[riyaapatel177]

not getting why i am getting this error - The MAX function only accepts a column reference as an argument.

P&L Values =

SWITCH( TRUE(),

MAX('P&L rows'[Order])=1, [Gross sales $]/1000000 ,

MAX('P&L rows'[Order])=2, [Pre_invoice deduction]/1000000 ,

MAX('P&L rows'[Order]=3), [Net Invoice $]/1000000 ,

MAX('P&L rows'[Order]=4), [Post Invoice discount]/1000000 ,

MAX('P&L rows'[Order]=5), [Post invoice other discount]/1000000 ,

MAX('P&L rows'[Order]=6), [Total Post invoice deduction]/1000000 ,

MAX('P&L rows'[Order]=7), [Net Sales $]/1000000 ,

MAX('P&L rows'[Order]=8), [Manufacturing cost]/1000000 ,

MAX('P&L rows'[Order]=9), [Freight cost]/1000000 ,

MAX('P&L rows'[Order]=10), [Other cost]/1000000 ,

MAX('P&L rows'[Order]=11), [Total COGS]/1000000 ,

MAX('P&L rows'[Order]=12), [Gross Margin]/1000000 ,

MAX('P&L rows'[Order]=13), [Gross margin %]/1000000 ,

MAX('P&L rows'[Order]=14), [Gross margin per unit]/1000000 ,

)

[ValtteriN · Accepted solution]

Hi,

You have a typo in some of your max functions:

MAX('P&L rows'[Order]=14),

The bracket is in the wrong place.


Also here is a revised dax:
measure =
var _max = 

MAX('P&L rows'[Order])
return

SWITCH( _max,

1, [Gross sales $]/1000000 ,

2, [Pre_invoice deduction]/1000000 ,
3, ...)

Since you are checking for the same condition you don't need TRUE(). Even if you want to use TRUE() I recommend using a variable.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/