topic Re: Column Values Comparison in DAX Commands and Tips

Column Values Comparison

SamuelROS — Tue, 01 Nov 2022 01:53:31 GMT

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.

Re: Column Values Comparison

amitchandak — Tue, 01 Nov 2022 02:11:07 GMT

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

23rd is only one record

Re: Column Values Comparison

SamuelROS — Tue, 01 Nov 2022 02:18:39 GMT

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

Re: Column Values Comparison

v-easonf-msft — Tue, 08 Nov 2022 08:19:34 GMT

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

Re: Column Values Comparison

SamuelROS — Tue, 08 Nov 2022 08:38:01 GMT

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