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

     

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