topic Re: Dax function for calculation using Previous value in DAX Commands and Tips

Dax function for calculation using Previous value

JayPee — Wed, 13 Jul 2022 14:56:47 GMT

Hi,

I am trying to achive the "Expected result" column in below table in one my Power Bi table, Kindly suggest is it possible to achive(Since I am new to Power Bi not sure about the correct function / logic to use here.)

For the first Expected result "12" the "Count / 10" considered is a Measure value from the same report.

Thanks

Re: Dax function for calculation using Previous value

Jihwan_Kim — Wed, 13 Jul 2022 16:26:19 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Expected result measure: = VAR _countmeasure = [Count measure:] VAR _result = SUMX ( FILTER ( ALL ( Data ), Data[Roll No] <= MAX ( Data[Roll No] ) ), Data[Basket A] + Data[Basket B] - Data[Basket C] ) RETURN IF ( HASONEVALUE ( Data[Roll No] ), _countmeasure + _result )

Re: Dax function for calculation using Previous value

JayPee — Wed, 13 Jul 2022 17:08:37 GMT

Thank you Jihwan_Kim It worked in my report as well. 🙂

Re: Dax function for calculation using Previous value

WinterMist — Wed, 13 Jul 2022 21:51:28 GMT

Jihwan_Kim

I downloaded your PBIX and can see that it's working.

However, there are a couple things I'm clearly not understanding.

Would you be able to educate me?

1) The final line of code is as follows: IF ( HASONEVALUE ( Data[Roll No] ), _countmeasure + _result )

- Why check to see if [Roll No] has a value? (Isn't every row going to have a value?)

- Why not skip the IF check, and simply RETURN _countmeasure + _result?

2) Per the requirements, only the first row uses [Count Measure] (value 10). The other rows don't use it at all. Instead, they call the previous value (as you know).

- But to me, it appears that your code is treating all rows the same.

- For all 3 rows in your code, HASONEVALUE will return True.

- Therefore, for all 3 rows, the same calculation is returned: _countmeasure + _result

- So how can the same calculation (which uses _countmeasure = 10) be used on all 3 rows, when _countmeasure should only be used on the first row?

3) Finally, I'm not grasping how the FILTER function is calling the previous value.

Data[Roll No] <= MAX ( Data[Roll No] ) --GET ROWS WHERE [Roll No] <= MAX [Roll No]

- FILTER is an iterator, right?

- So it iterates through each row of the 'Data' table, performing the "<=" expression.

- For Row 1:
- Data[Roll No] = 6

- MAX(Data[RollNo]) = 8

- 6 <= 8 evaluates to True. Therefore, this row is kept in the filter.

- For Row 2:
- Data[Roll No] = 7

- MAX(Data[RollNo]) = 8

- 6 <= 8 evaluates to True. Therefore, this row is kept in the filter.

- For Row 3:
- Data[Roll No] = 8

- MAX(Data[RollNo]) = 8

- 8 <= 8 evaluates to True. Therefore, this row is kept in the filter.

- As a result, all 3 rows are kept in the filter.

- Am I reading the code correctly here?

- Regardless, for each row, how is the value from the previous row being used in the calculation?

I really want to understand this. Your help would be greatly appreciated.

Regards,

Nathan

Re: Dax function for calculation using Previous value

Jihwan_Kim — Thu, 14 Jul 2022 03:04:48 GMT

Hi,

Thank you for your message.

1. On each Roll No., a result is shown. But how do you want to show a result on TOTAL line? I did not know whether you want to show it as total (12+15+31) or something else, so I left it as blank by adding a condition with using hasonevalue dax function.

2. In my opinion, value 10 is used for all Roll No..

Roll No.6 = value 10 + 1 + 4 - 3

Roll No.7 = RollNo6 + 9 + 2 - 8 = value 10 + 1 + 4 - 3 + 9 + 2 - 8

Roll No.8 = RollNo7 + 13 + 9 - 6 = value 10 + 1 + 4 - 3 + 9 + 2 - 8 + 13 + 9 - 6

3. I corrected in red color in below.

- For Row 1:
- Data[Roll No] = 6

- MAX(Data[RollNo]) = 6

- 6 <= 6 evaluates to True. Therefore, this row is kept in the filter.

- For Row 2:
- Data[Roll No] = 7

- MAX(Data[RollNo]) = 7

- 6 and 7 <= 7 evaluates to True. Therefore, this row including previous row are kept in the filter.

- For Row 3:
- Data[Roll No] = 8

- MAX(Data[RollNo]) = 8

- 6, 7, and 8 <= 8 evaluates to True. Therefore, this row including preivous rows are kept in the filter.

I hope it gives some idea.

Thank you.

Re: Dax function for calculation using Previous value

WinterMist — Thu, 14 Jul 2022 13:18:30 GMT

Jihwan_Kim

Thank you very much for the lesson.

This helps greatly!

Regards,

Nathan