Forum Discussion

lifesafari's avatar
lifesafari
Icon for Helper I rankHelper I
3 years ago
Solved

Help on DAX forumla

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 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

5 Replies

  • WinterMist's avatar
    WinterMist
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    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

    • lifesafari's avatar
      lifesafari
      Icon for Helper I rankHelper I
      3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

    • lifesafari's avatar
      lifesafari
      Icon for Helper I rankHelper I
      3 years ago

      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 

  • WinterMist's avatar
    WinterMist
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    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