Forum Discussion

DanielB_NL's avatar
DanielB_NL
Icon for Helper I rankHelper I
2 years ago

Adding conditional column with either value from other column OR the value from the row above

Hi All,  I'm still trying to find my way in PowerBi and to solve some interesting questions, which I have.   I have this query that generates the upper table: it put some expected mutations for a ...
Need Help
DanielB_NL's avatar
DanielB_NL
Icon for Helper I rankHelper I
2 years ago

I'm working with swicht and nesteds IF's, but I guess I have to start with getting the value of 'reference' for the row direct after a Purchase row: I've tried and googled a while, and came up with the code below, but it's not working.

 

previous_purchase = VAR CurrentRowType = Todays_future[type] VAR PreviousRowType = CALCULATE(MAX(Todays_future[type]), FILTER(ALL(Todays_future), Todays_future[index] = EARLIER(Todays_future[index]) - 1)) RETURN IF(CurrentRowType = "Purchase (PO)", Todays_future[reference], BLANK())

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Try not to use EARLIER.  Use variables instead.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • DanielB_NL's avatar
    DanielB_NL
    Icon for Helper I rankHelper I
    2 years ago

    Hi, thank you for the swift reaction. Variables is competely new to me, so I really hope you can help me a little further. I've created a small dataset in an CSV-file and made it available from my Dropbox account:

    https://www.dropbox.com/scl/fi/rfa20o4q1uq157ha9gdwg/use_case_PowerBI_data.csv?rlkey=i2x9ag9asd99m0g4pdmc29cb4&st=uh2gnb5o

    I've created an Excelsheet with the desired results:

    https://www.dropbox.com/scl/fi/ufdqndnkkoozuqcesk79x/use_case_PowerBI_desired_result.xlsx?rlkey=yfeo19f19xdxy6tlnepez8t80&st=tz7ym6gq

     

    If I have the extra columns, in my Dashboard I can filter on rows that have in the column 'Stock_after_booking' a value < 0. By doing this I isolate all my project which have a potential problem and I also see which Purchase Orders I can try to influence in order to solve the problem. 

     

    I really hope you can help me with this, thanks in advance for your reply!

    Kind regards, Daniel

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Thank you for the sample data. Looking at it I don't see any fields that would be impacted by user filter choices.  As such the new columns could be added in Power Query instead of DAX. Do you have any preferences?

      • DanielB_NL's avatar
        DanielB_NL
        Icon for Helper I rankHelper I
        2 years ago

        Hi lbendlin, PowerQuery would be fine as wel!! I don't have any preferences and am already very happy that you are putting your time and effort in it