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.

     

maddaelle's avatar
maddaelle
Frequent Visitor
3 years ago

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.

 

 

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.

 

 

Can someone help me with that?

Thank you,

 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
    maddaelle
    Frequent Visitor
    3 years ago

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