Forum Discussion
Dax function for calculation using Previous value
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
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 )
5 Replies
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 )Thank you Jihwan_Kim It worked in my report as well. 🙂
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
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.
