xLookup in power bi

[Anonymous]

Hello Community,

I have done this in Excel before but I want to do it in Power BI

So I have 3 Columns Article, Quantity OC, Free Quantity and I want to calculate Quantity available based on these columns 

This is the result I want: 

if the Article showed for the first time I want to take the Quantity Libre, if the article appear again I want the first difference between Quantity Libre and Quantity OC which is 5 in my example and if the article appear again so it's gonna be negative I want it 0 instead of showing negative number. 

I use this formula in excel: 

Hope that you can help me 

Thanks a lot

Malek

[Anonymous]

Thank you @v-stephen-msft so helpful 🙂

[v-stephen-msft · Accepted solution]

Hi  @Anonymous ,

First, implementing iteration in DAX is extremely complex. If your data is as simple as you provided, you can create an index column group by Article and do two calculations to get the final result. Otherwise, it is recommended that you get the results in Excel and import them into Power BI.

1.Create the index column in Power Query. Reference: How to create group index with Power Query or R - (example in Power BI)

2.Do the first calculation.

3.The second to get the final result.

Please download the attachmen for more details.

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.