Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello BI Gurus,
I'm fairly new to DAX and I have been trying to wrap around my head on how to accomplish the following;
How can I determine if a record conflicts base on the following conditions;
1. Date are the same
2. "Ename" has values from other record but different column "Interim"
Any insight is greatly appreciated.
Solved! Go to Solution.
Hi, @SamuelROS
Please try calculated column like:
Result =
VAR _count =
CALCULATE (
DISTINCTCOUNT('Table'[Interim]),
FILTER (
ALL ( 'Table' ),
'Table'[Leave Date] = EARLIER ( 'Table'[Leave Date] )
&& 'Table'[Leave Type] = "Leave"
&& 'Table'[Interim] <> EARLIER ( 'Table'[E Name] )
)
)
RETURN
IF ( _count > 0, "conflicts" )
If it doesn't work, please share your expected output in excel.
Best Regards,
Community Support Team _ Eason
@SamuelROS , 17th also has the same issue, why not that ?
23rd is only one record
Hi @amitchandak ,
yes so basically I'm after all records that has the same date && values in Ename can be found in the record of any of the same date's Interim column. What i'm trying to achieve is to determine if a user's [E Name] leave date conflicts with his Interim [Interim].
Hi, @SamuelROS
Please try calculated column like:
Result =
VAR _count =
CALCULATE (
DISTINCTCOUNT('Table'[Interim]),
FILTER (
ALL ( 'Table' ),
'Table'[Leave Date] = EARLIER ( 'Table'[Leave Date] )
&& 'Table'[Leave Type] = "Leave"
&& 'Table'[Interim] <> EARLIER ( 'Table'[E Name] )
)
)
RETURN
IF ( _count > 0, "conflicts" )
If it doesn't work, please share your expected output in excel.
Best Regards,
Community Support Team _ Eason
Hi CST,
It didn't work the way I wanted it to since it was returning the same values. But I did revised it a bit and it work like a charm :). Thank you for taking the time to look into my problem. Your code was also very easy to understand.
Result =
VAR _count =
CALCULATE (
DISTINCTCOUNT('Table'[Leave Date]),
FILTER (
ALL ( 'Table' ),
'Table'[Leave Date] = EARLIER ( 'Table'[Leave Date] )
&& 'Table'[Interim] = EARLIER ( 'Table'[E Name] )
)
)
RETURN
IF ( _count > 0, "Conflict","No Conflict" )
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |