Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JayPee
Regular Visitor

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.

 

JayPee_0-1657724065918.png

 

Thanks

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

 

Untitled.png

 

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 )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

5 REPLIES 5
WinterMist
Impactful Individual
Impactful Individual

@Jihwan_Kim 

 

Thank you very much for the lesson.

This helps greatly!

 

Regards,

Nathan

WinterMist
Impactful Individual
Impactful Individual

@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?

 

WinterMist_0-1657747319458.png

 

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?

 

WinterMist_1-1657747664182.png

 

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

 

WinterMist_2-1657748246366.png

 

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.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

 

Untitled.png

 

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 )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.