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 condit...
  • 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

     

amitchandak's avatar
amitchandak
Super User
3 years ago

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

23rd is only one record

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

     

    • SamuelROS's avatar
      SamuelROS
      Frequent Visitor
      3 years ago

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