topic Re: Help on DAX forumla in DAX Commands and Tips

Help on DAX forumla

lifesafari — Fri, 14 Jul 2023 13:04:14 GMT

Hello i need a create a DAX to use in a card but i have this error

With the forumula i need to calculate the KPI PMP and i've create a new measure as is:

PMP1 = DIVIDE( CALCULATE([Machine Minutes], DimMachineStatus[MachineStatusId] = 16), CALCULATE([Machine Minutes], DimMachineStatus[MachineStatusId] IN {3,4,16}) )

Where wrong? Thanks

Re: Help on DAX forumla

WinterMist — Fri, 14 Jul 2023 13:19:02 GMT

lifesafari

To troubleshoot,

1) Try separating each CALCULATE into its own separate measure to isolate where the error is.

2) If the error still occurs in each separate measure, remove the DimMachineStatus condition line to see if it still throws the error.

Are you able to 100% verify the data type of DimMachineStatus[MachineStatusId]?

If it happens to be data type TEXT, that would cause it since your measure is evaluating INTEGERS. (16 & {3,4,16})

Regards,

Nathan

Re: Help on DAX forumla

Anonymous — Fri, 14 Jul 2023 14:18:24 GMT

PMP1 =
VAR var1 = CALCULATE([Machine Minutes], DimMachineStatus[MachineStatusId] = 16)
VAR var2 = CALCULATE([Machine Minutes], DimMachineStatus[MachineStatusId] IN {3,4,16})
VAR var3 = DIVIDE(var1, var2)
RETURN
var3 //but as you have an error check var1 and var1 return without errors.

Re: Help on DAX forumla

lifesafari — Fri, 14 Jul 2023 14:41:09 GMT

Hello and thanks for reply. Yes DimMachineStatus[MachineStatusId] is a TEXT type...

Re: Help on DAX forumla

WinterMist — Fri, 14 Jul 2023 17:58:38 GMT

lifesafari

OK, then yes. That's the problem.

If a column contains text, then DAX cannot compare it to an integer.

You must compare text to text or integers with integers.

You should be able to resolve this by changing the integers to strings within your measure, as follows.

PMP1 =
DIVIDE(
CALCULATE(
[Machine Minutes],
DimMachineStatus[MachineStatusId] = "16"
),
CALCULATE(
[Machine Minutes],
DimMachineStatus[MachineStatusId] IN {"3", "4" , "16"}
)
)

Regards,

Nathan

Re: Help on DAX forumla

lifesafari — Wed, 19 Jul 2023 13:07:56 GMT

Hello and thanks for reply.

I try to divide the forumla and also use your proposal but have error.

If i use

PMP2 = CALCULATE([Machine Minutes], DimMachineStatus[MachineStatusId] = 16)

Retourn this error

For more info:

Machine Minutes is the name of column in table "Measure" and is a number value

MachineStatusId is a text type of table DimMachineStatus

I need calculate this:

Need to extract from colum "Machine Minutes" the value of filtre 16 on MachineStatusID (VALUE 1)
Then divide VALUE 1 with the sum of value MachineStatusID 16, 3,4

Thanks