topic Re: Increment value with condition in DAX Commands and Tips

Increment value with condition

jc173 — Mon, 19 Feb 2024 11:29:10 GMT

Hi guys i have this situation:

I would like to get this,

1	1
0	0
1	2
1	2
1	2
0	0
1	3
1	3

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

Thanks

Re: Increment value with condition

123abc — Mon, 19 Feb 2024 13:26:43 GMT

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:

Create a new calculated column by clicking on "Modeling" in the top menu, then selecting "New Column".
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.

Re: Increment value with condition

jc173 — Mon, 19 Feb 2024 14:09:54 GMT

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

Re: Increment value with condition

123abc — Tue, 20 Feb 2024 05:30:53 GMT

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.

Re: Increment value with condition

Anonymous — Tue, 20 Feb 2024 07:25:26 GMT

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.