Forum Discussion

maddaelle's avatar
maddaelle
Frequent Visitor
3 years ago
Solved

Need help with my code - incremental calculated column DAX

Hi everyone,  I need help fixing my code for an incremental column based on 2 conditions.   Here it is:   It is not working properly because you can see in row 16 I don't want to be shown ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi maddaelle ,

     

    Please try this code to create a calculated column.

    Column 3 = 
VAR _Step1 =
    ADDCOLUMNS (
        'Table',
        "Step1",
            SWITCH (
                TRUE (),
                'Table'[A] = 1
                    && 'Table'[B] = 0, 1,
                'Table'[A] = 0
                    && OR ( 'Table'[B] = 0, 'Table'[B] = 1 ), 0
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "Step2",
            MAXX (
                FILTER ( _Step1, [Index] < EARLIER ( [Index] ) && NOT ( ISBLANK ( [Step1] ) ) ),
                [Index]
            )
    )
VAR _Step3 =
    ADDCOLUMNS (
        _Step2,
        "Step3",
            VAR _VALUE =
                SUMX ( FILTER ( _Step2, [Index] = EARLIER ( [Step2] ) ), [Step1] )
            RETURN
                IF ( ISBLANK ( [Step1] ), _VALUE + [Index] - [Step2], [Step1] )
    )
RETURN
    SUMX ( FILTER ( _Step3, [Index] = EARLIER ( 'Table'[Index] ) ), [Step3] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

amitchandak's avatar
amitchandak
Super User
3 years ago

maddaelle , can you provide logic, what are you trying to achieve

The information you have provided is not making the problem clear to me. Can you please explain with an example.
For a faster solutions refer https://www.youtube.com/watch?v=UrFuZ2uHjdY
Appreciate your Kudos.

  • maddaelle's avatar
    maddaelle
    Frequent Visitor
    3 years ago

    Hi amitchandak, thanks for answering.

     

    The Logic I want to implement in column 3 Is this:

     

    First case : IF A = 1AND B = 0 THEN 1

    Second case : IF A = 0 AND B = 0 or 1 THEN 0

    Third case : IF A = 1 AND B = 1 COLUMN3[PREVIOUS ROW] =+ 1

     

    In ROW 16 there Is an error since the previous ROW value Is 1 so i want It to give back 2 (1+1).

     

    Line 14 Is correct, line 15 also because it gets the result for second condition, but line 16 should make the incrementale work.

     

    Example

    A  B  C3

           1. 0.  1

    0. 1.  0

    0.  0.  0

    1.  1.  1

    1.  0.  1

    1.  1.  2

    1.  1.  3

    1.  1.  4

    1.  0.  1

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi maddaelle ,

       

      Please try this code to create a calculated column.

      Column 3 = 
VAR _Step1 =
    ADDCOLUMNS (
        'Table',
        "Step1",
            SWITCH (
                TRUE (),
                'Table'[A] = 1
                    && 'Table'[B] = 0, 1,
                'Table'[A] = 0
                    && OR ( 'Table'[B] = 0, 'Table'[B] = 1 ), 0
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "Step2",
            MAXX (
                FILTER ( _Step1, [Index] < EARLIER ( [Index] ) && NOT ( ISBLANK ( [Step1] ) ) ),
                [Index]
            )
    )
VAR _Step3 =
    ADDCOLUMNS (
        _Step2,
        "Step3",
            VAR _VALUE =
                SUMX ( FILTER ( _Step2, [Index] = EARLIER ( [Step2] ) ), [Step1] )
            RETURN
                IF ( ISBLANK ( [Step1] ), _VALUE + [Index] - [Step2], [Step1] )
    )
RETURN
    SUMX ( FILTER ( _Step3, [Index] = EARLIER ( 'Table'[Index] ) ), [Step3] )

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

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

       

      • maddaelle's avatar
        maddaelle
        Frequent Visitor
        3 years ago

        Hi Anonymous , I adapted your steps to my file but I can't say if it works because I need to filter my table by a field called Name.
        So your code for column3 would need a filter by Name for each step?

        I am facing errors when trying to insert that in your code.