Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@Anonymous
It seems that your days columns are datatype text.
Change them to number
@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 ) )
@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 ) )
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] )
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.
@Anonymous
It seems that your days columns are datatype text.
Change them to number
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |