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 leave if leave days less than or equal to 150 days(want remaining days if leave taken greater than 150days).

The marriage leave should not consider as a leave if leave days less than or equal to 5 days(want remaining days if leave taken days greater than 5 days).

 

 

                                                 LEAVE DETAILS

 

Request type                      Leave taken days                    Expected column

 

Work FromHome                   15                                                 0

Maternity leave                      180                                               30

Marriage leave                       10                                                 5

 

Query:

 

Expected Column = if('Leave Details'[Request type] = "Work From Home" && if('Leave Details'[Leave taken days] >0,0,if('Leave Details'[Request type] = "Maternity" && if('Leave Details'[Leave taken days]>=150,'Leave Details'[Leave taken days] - 150,Leave Details'[Leave taken days]))))

 

Regards,

Yuvaraj

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 )
)

4 Replies

  • 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

         

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    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 )
)