Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculated column using multiple conditions

Hi   I have columns in a table like Request type and Leave taken days but the request type as work from home should not to be consider as a leave. The maternity leave should not consider as a leav...
Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    Anonymous

     

    It seems that your days columns are datatype text.

     

    Change them to number

     

  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous,

     

    You may use DAX below.

    Column =
SWITCH (
    'LEAVE DETAILS'[Request type],
    "Work FromHome", 0,
    "Maternity leave", MAX ( 0, 'LEAVE DETAILS'[Leave taken days] - 150 ),
    "Marriage leave", MAX ( 0, 'LEAVE DETAILS'[Leave taken days] - 5 )
)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

I would use SWITCH

 

Expected Column = 
SWITCH(TRUE(),
 'Leave Details'[Request type] = "Work From Home" && 'Leave Details'[Leave taken days] >0,0,
'Leave Details'[Request type] = "Maternity" && 'Leave Details'[Leave taken days]>=150,'Leave Details'[Leave taken days] - 150,
'Leave Details'[Request type] = "Marriage" && 'Leave Details'[Leave taken days]>=5,'Leave Details'[Leave taken days] - 5,
'Leave Details'[Request type] = "Maternity" && 'Leave Details'[Leave taken days]<150,0,
'Leave Details'[Request type] = "Marriage" && 'Leave Details'[Leave taken days]<5,0,
Leave Details'[Leave taken days]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI,

     

    error occurs,

     

    Dax comparison operations do not support comparing values of type text with values of type integer. Consider using the VALUE or FORMAT function to convert one of the values.

     

    • themistoklis's avatar
      themistoklis
      Community Champion
      7 years ago

      Anonymous

       

      It seems that your days columns are datatype text.

       

      Change them to number