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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi maddaelle ,

         

        According to your sample, I couldn't find [Name] column. If your calculation need filter based on [Name], you may need to add name filter in each step. I think you can try ALLEXCEPT() function on table in step1. 

        If this reply still couldn't help you solve your issue, please show me the whole table with [Name] and show me a screenshot with the result you want. This will make me easier to find the solution.

         

        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.