Forum Discussion

jc173's avatar
jc173
Frequent Visitor
2 years ago
Solved

Increment value with condition

Hi guys i have this situation:

1
0
1
1
1
0
1

I would like to get this,

11
00
12
12
12
00
13
13

Every time i have a 0 value i need to increment the value that i sum at the column.

Thanks

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jc173 ,

    Based on your questions, here are my answers.

    Firstly, create a table as you mentioned.

    Then go to the Power Query and select the Index Column.

    Enter the appropriate DAX code to get the results you need.

     

    Column = 
IF (
    'Table'[Column1] <> 0,
    RANKX ( FILTER ( 'Table', 'Table'[Column1] = 0 ), 'Table'[Index],,ASC ),
    0
)

     

     

     

    Best Regards

    Yilong Zhou

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

4 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    To achieve the desired result in Power BI using DAX, you can create a calculated column to track the cumulative sum based on the condition of encountering a 0 value. Here's how you can do it:

    Assuming your table is named "Table1" and the column containing the original values is named "Value", you can follow these steps:

    1. Create a new calculated column by clicking on "Modeling" in the top menu, then selecting "New Column".

    2. Use the following DAX expression to create the calculated column:

    CumulativeSum =
    VAR CurrentValue = Table1[Value]
    VAR CumulativeSum =
    CALCULATE(
    SUM(Table1[Value]),
    FILTER(
    ALL(Table1),
    Table1[Value] <> 0 &&
    Table1[Value] <= CurrentValue
    )
    )
    RETURN
    IF(Table1[Value] = 0, 0, CumulativeSum)

     

    Explanation:

    • The VAR CurrentValue stores the current value of the row.
    • The VAR CumulativeSum calculates the cumulative sum of values that meet the conditions: not equal to 0 and less than or equal to the current value.
    • The IF function checks if the current value is 0. If it is, it returns 0; otherwise, it returns the CumulativeSum.

    After creating this calculated column, you should see the desired result where the cumulative sum increments only when encountering a 0 value.

     

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

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

    • jc173's avatar
      jc173
      Frequent Visitor
      2 years ago

      Hi thanks for the replie, but i'am on power bi online (Data FActory) and i don't have the possibility to insert a calculated column

      • 123abc's avatar
        123abc
        Community Champion
        2 years ago

        If you're working in Power BI online or using Data Factory where you can't create calculated columns directly, you can achieve the desired outcome using DAX measures. Here's how you can do it:

        Assuming you have a table named "YourTable" with a column named "Value" that contains your values, you can create a measure using DAX to compute the incremented values based on your condition.

        Here's the DAX measure you can use:

         

        Incremented Value =
        VAR MaxRowIndex = MAX('YourTable'[Index])
        VAR PrevIncrement =
        CALCULATE(
        MAX('YourTable'[Incremented Value]),
        FILTER(
        ALL('YourTable'),
        'YourTable'[Index] < MaxRowIndex &&
        'YourTable'[Value] = 0
        )
        )
        RETURN
        IF('YourTable'[Value] = 0, PrevIncrement + 1, PrevIncrement)

         

         

        Make sure to replace 'YourTable', 'Index', 'Value', and 'Incremented Value' with the actual names of your table and columns.

        This measure works as follows:

        • It finds the maximum index value for the current row.
        • It calculates the previous increment value based on the condition that the index is less than the current row's index and the value is 0.
        • If the value for the current row is 0, it increments the previous increment value by 1; otherwise, it returns the previous increment value.

        You can then use this measure in your visuals to display the incremented values based on your condition.

         

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

         

        In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jc173 ,

    Based on your questions, here are my answers.

    Firstly, create a table as you mentioned.

    Then go to the Power Query and select the Index Column.

    Enter the appropriate DAX code to get the results you need.

     

    Column = 
IF (
    'Table'[Column1] <> 0,
    RANKX ( FILTER ( 'Table', 'Table'[Column1] = 0 ), 'Table'[Index],,ASC ),
    0
)

     

     

     

    Best Regards

    Yilong Zhou

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