Forum Discussion

wingsted93's avatar
wingsted93
Frequent Visitor
5 years ago
Solved

comparing rows values for conditional column in power query

I have data like follows: The 'EndDatePopulated' is the column that im trying to create in power query using M. I want the EndDate if it is present. If it is not present it is either because t...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi wingsted93 ,

    Besides using power query like @ Rocco_sprmnt21  mentioned, you can also using DAX to create a calculated column to achieve this:

    EndDatePopulated =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    IF (
        _count >= 2,
        SWITCH (
            TRUE (),
            [StartDate]
                = CALCULATE (
                    MIN ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ),
                CALCULATE (
                    MAX ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ) - 1,
            DATE ( 9999, 1, 1 )
        ),
        SWITCH (
            TRUE (),
            ISBLANK ( 'Table'[EndDate] ), DATE ( 9999, 1, 1 ),
            'Table'[EndDate]
        )
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi wingsted93 ,

Besides using power query like @ Rocco_sprmnt21  mentioned, you can also using DAX to create a calculated column to achieve this:

EndDatePopulated =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    IF (
        _count >= 2,
        SWITCH (
            TRUE (),
            [StartDate]
                = CALCULATE (
                    MIN ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ),
                CALCULATE (
                    MAX ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ) - 1,
            DATE ( 9999, 1, 1 )
        ),
        SWITCH (
            TRUE (),
            ISBLANK ( 'Table'[EndDate] ), DATE ( 9999, 1, 1 ),
            'Table'[EndDate]
        )
    )

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.