Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Add colunma IF Conditional with DAX

Hello, thank you explain coo add a conditioned column using DAX.

Here is the example and the coumna C would be the DAX's response:

IDYearCondition Result
A2018Continue
A2019Continue
A2020Continue
A2021Continue
B2018Left
C2018Continue
C2019Continue
C2020Continue
C2021Continue
D2019new
D2020left

Thank you very much and greetings from Brisbane Australia.

Rosario.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Syndicate_Admin ,

     

    Please firstly create a new table:

    New Table =
VAR _all =
    CROSSJOIN ( VALUES ( 'Table'[ID] ), VALUES ( 'Table'[Year] ) )
VAR _t =
    ADDCOLUMNS (
        _all,
        "min",
            CALCULATE (
                MIN ( 'Table'[Year] ),
                FILTER ( _all, [ID] = EARLIER ( 'Table'[ID] ) )
            )
    )
VAR _maxYear =
    MAXX ( ALL ( 'Table' ), [Year] )
RETURN
    SELECTCOLUMNS (
        FILTER ( _t, [Year] >= [min] && [Year] <= _maxYear ),
        "ID", [ID],
        "Year", [Year],
        "Status", LOOKUPVALUE ( 'Table'[Condition Result], [ID], [ID], [Year], [Year] )
    )

    Then add a new column:

    Final =
VAR _t =
    SUMMARIZE (
        FILTER ( 'New Table', [ID] = EARLIER ( 'New Table'[ID] ) ),
        [Status]
    )
VAR _lastYear =
    MAXX (
        FILTER (
            'New Table',
            [ID] = EARLIER ( 'New Table'[ID] )
                && [Status] <> BLANK ()
        ),
        [Year]
    )
VAR _lastStatus =
    LOOKUPVALUE ( 'New Table'[Status], [ID], [ID], [Year], _lastYear )
RETURN
    IF (
        [Status] = BLANK (),
        IF ( ( "Continue" IN _t ) = FALSE, _lastStatus ),
        [Status]
    )

    Final output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Thank you Samarth for your support, from my example table I want to get column C, Condition Result

      Rosario_1-1650839812667.png

      Best regards

      Rosario

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    Please firstly create a new table:

    New Table =
VAR _all =
    CROSSJOIN ( VALUES ( 'Table'[ID] ), VALUES ( 'Table'[Year] ) )
VAR _t =
    ADDCOLUMNS (
        _all,
        "min",
            CALCULATE (
                MIN ( 'Table'[Year] ),
                FILTER ( _all, [ID] = EARLIER ( 'Table'[ID] ) )
            )
    )
VAR _maxYear =
    MAXX ( ALL ( 'Table' ), [Year] )
RETURN
    SELECTCOLUMNS (
        FILTER ( _t, [Year] >= [min] && [Year] <= _maxYear ),
        "ID", [ID],
        "Year", [Year],
        "Status", LOOKUPVALUE ( 'Table'[Condition Result], [ID], [ID], [Year], [Year] )
    )

    Then add a new column:

    Final =
VAR _t =
    SUMMARIZE (
        FILTER ( 'New Table', [ID] = EARLIER ( 'New Table'[ID] ) ),
        [Status]
    )
VAR _lastYear =
    MAXX (
        FILTER (
            'New Table',
            [ID] = EARLIER ( 'New Table'[ID] )
                && [Status] <> BLANK ()
        ),
        [Year]
    )
VAR _lastStatus =
    LOOKUPVALUE ( 'New Table'[Status], [ID], [ID], [Year], _lastYear )
RETURN
    IF (
        [Status] = BLANK (),
        IF ( ( "Continue" IN _t ) = FALSE, _lastStatus ),
        [Status]
    )

    Final output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.