Column Values Comparison

[SamuelROS]

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.

[amitchandak]

@SamuelROS , 17th also has the same issue, why not that ?

23rd is only one record

[SamuelROS]

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

[v-easonf-msft · Accepted 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]

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