cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maddaelle
Frequent Visitor

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:

maddaelle_0-1669673470972.jpeg

 

It is not working properly because you can see in row 16 I don't want to be shown a 3, but a 2. You see it is happening because the column is based on a calculation that gives back the difference between number of rows so I need to add one condition to my code in order to fix this. The problem is, I can't figure out how.

 

This is the code:

 

Column 3 = 

VAR c = 'Tot MD_TEST'[Data]

VAR pre =

    CALCULATE (

        MAX ( 'Tot MD_TEST'[Data] ),

        FILTER ( 'Tot MD_TEST',

        'Tot MD_TEST'[Data] < c

        && 'Tot MD_TEST'[A] = 0

        && 'Tot MD_TEST'[Name] = EARLIER('Tot MD_TEST'[Name])

    )

    )

VAR data_prec =

    CALCULATE(

           MAX('Tot MD_TEST'[Data]),

           FILTER(

               'Tot MD_TEST','Tot MD_TEST'[Name] = EARLIER('Tot MD_TEST'[Name]) &&

               'Tot MD_TEST'[Data] < EARLIER('Tot MD_TEST'[Data])

           )

          )

RETURN

    IF (

        'Tot MD_TEST'[A] <> BLANK (),

        IF('Tot MD_TEST'[A] = 1,

        IF('Tot MD_TEST'[B] = 1,
 

        CALCULATE (

            COUNTROWS ( 'Tot MD_TEST' ),

            FILTER (

                'Tot MD_TEST',

                'Tot MD_TEST'[Data] <= EARLIER('Tot MD_TEST'[Data])

                && 'Tot MD_TEST'[A] <> 0

               

                && 'Tot MD_TEST'[Name] = EARLIER('Tot MD_TEST'[Name])

            )

        )

            - CALCULATE ( MAX ( 'Tot MD_TEST'[Column] ), FILTER ( 'Tot MD_TEST', 'Tot MD_TEST'[Data] = pre && 'Tot MD_TEST'[Name] = EARLIER('Tot MD_TEST'[Name])) ),

            1),

            0

    ))

 

Thank you!

1 ACCEPTED SOLUTION

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.

RicoZhou_0-1669708258308.png

 

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.

 

View solution in original post

12 REPLIES 12
maddaelle
Frequent Visitor

Hi everyone,

 

I am a newbie in powerbi and struggling with a quite complex task.

I need to get a measure of "chain sessions" per user that I want to use for conditional formatting later and here is how it should work.

 

Immagine 2022-11-21 181033.jpg

 

I need to get in Powerbi these 3 columns or calculations:

  • column A that measures if the session duration (per user) is >= 45 minutes 
  • column B that measures if the difference between this session and previous session (per user) is < 5 days.
  • column C contains the logic on how I want to get my chain session measured.

 

I am struggling understanding how to get the same thing done into powerbi, not sure if I should choose a calculated column or a measure since all of this measure should also consider the UserID in that table, should be calculated on the previous row for that specific UserID.

 

This could be the whole table, please note that I added first 3 columns only to get a general understanding, it does not work with calculations I made before.
Every day I get the sessions for each UserID.

 

Immagine 2022-11-22 092222.jpg

 

Can someone help me with that?

Thank you,

 

Thank you, I appreciate your help, but I was looking for something with DAX

Hi, @maddaelle ;

You can refer to the following recursive functions for power query, such as @fx(x-1);

M Language Functions - PowerQuery M | Microsoft Learn


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

maddaelle
Frequent Visitor

Hi, 

I posted my first post last week and worked on it, now I need some help trying to fix my code which is almost working.

I found this post previously https://community.powerbi.com/t5/Desktop/How-can-I-write-a-formula-for-incremental-addition-similar-... and applied  @v-diye-msft  's answer to my code.

 

It's a little bit different since I have to conditions to satisfy in order to get the incremental calc.

 

I now have column A and which contain the conditions I need to verify (1 is true and 0 is false).

Then I need my calculation to do this:

if (A = 1, if (B = 1, incremental calc, 1), 0)

 

Saw in that solution I needed to create 2 columns to do so. Here they are:

maddaelle_0-1669645181782.png

 

As you can see, my Column 3, the one with the incremental calculation, is not working but I can't fix the error. 


This is the code for Column:

 

Column = 

CALCULATE (

    COUNTROWS ( 'Table' ),

    FILTER (

        'Table',

        'Table'[Data] <= EARLIER ( 'Table'[Data] )

            && 'Table'[A] = 1 && 'Table'[B] = 1

    ), FILTER('Table','Table'[Name] = EARLIER('Table'[Name])))

And this is the code for Column 3:
 
Column 3 = 

VAR c = 'Table'[Data]

VAR pre =

    CALCULATE (

        MAX ( 'Table'[Data] ),

        FILTER ( 'Table', 'Table'[Data] < c && 'Table'[A] = 0 && 'Table'[B] = 0 ),

        FILTER('Table','Table'[Name] = EARLIER('Table'[Name])

    ))

RETURN

    IF (

        'Table'[A] = 1, if( 'Table'[B] = 1,

        CALCULATE (

            COUNTROWS ( 'Table' ),

            FILTER('Table',

                    'Table'[Name] = EARLIER('Table'[Name])),

            FILTER (

                'Table',

                'Table'[Data] <= EARLIER ( 'Table'[Data] )

                   && 'Table'[B] <> 0 && 'Table'[A] <> 0

            -
CALCULATE ( MAX ( 'Table'[Column] ), FILTER ( 'Table', 'Table'[Data] = pre && 'Table'[B] <> 0 && 'Table'[A] <> 0) ))), 1), 0)

Hi @maddaelle 

 

Can you show the expected column along with the current sample data? At present I don't understand why "Column 3" is not working. According to my understanding of the formula "if (A = 1, if (B = 1, incremental calc, 1), 0)", the curent "Column 3" in the image seems to have returned the correct results. Which values are not correct and what are the desired values?

 

Best Regards,
Community Support Team _ Jing

Hi, thanks for your answer, I just updated all this work in another post, if you could check.
https://community.powerbi.com/t5/Desktop/Need-help-with-my-code-incremental-calculated-column-DAX/m-...

amitchandak
Super User
Super User

@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.

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

 

 

 

 

 

 

 

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.

RicoZhou_0-1669708258308.png

 

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.

 

Hi @RicoZhou , 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.

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.

Hi @RicoZhou , 
thanks for your answer. I achieved filtering the table by adding a variable to your code = the filtered table and then using it inside the functions of your code.

 

 Now it works fine for me.

So thank you very much for your help, I am marking it as the solution for my post.

Still, if you had some time, I would really appreciate a little explanation on how you were able to make the reasoning for my case and to solve it this way! Is there any source where I can read about this "steps" coding? 

 

Again, thanks.

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors