Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS

@Anonymous

 

It seems that your days columns are datatype text.

 

Change them to number

 

image.png

View solution in original post

v-chuncz-msft
Community Support
Community Support

@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 )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@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 )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.Screenshot_2.png

 

@Anonymous

 

It seems that your days columns are datatype text.

 

Change them to number

 

image.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.