Forum Discussion

SamuelROS's avatar
SamuelROS
Frequent Visitor
3 years ago
Solved

Column Values Comparison

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.

 

 

  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

     

4 Replies

    • SamuelROS's avatar
      SamuelROS
      Frequent Visitor
      3 years ago

      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's avatar
        v-easonf-msft
        Community Support
        3 years ago

        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