Dax function for calculation using Previous value

[JayPee]

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

[WinterMist]

@Jihwan_Kim 

Thank you very much for the lesson.

This helps greatly!

Regards,

Nathan

[WinterMist]

@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

[Jihwan_Kim]

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.

[Jihwan_Kim · Accepted solution]

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 )

[JayPee]

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