Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |