locked
DAX 101 Issue RRS feed

  • Question

  • Hi,

    Apologies, I'm fairly sure this is DAX 101 (context issue) but I'm really stuck.

    We've got 3 tables: 1. Warehouse SOH, 2. Products 3. Picklists (AKA orders)

    The tables are linked by SKU. 

    Each Picklist row specifies a SKU, no. of items to pick (PickAmt), store they're being picked for and the state.  We would like to create an "Adjusted Pick" for each of these rows that adjusts the PickAmt based on the SOH in the warehouses.  We need to:

    1. Sum the PickAmt for each SKU in each state (TotalSKUPickByState)

    2. Subtract the TotalSKUPickByState from the SOH for each SKU in the Warehouse in the State (RemainingSKUSOHByState)

    3. If RemainingSKUSOHByState is:

    a) >= 0 then AdjustedPick = PickAmt

    b) <0 then AdjustedPick = 0

    At this stage I've spent a couple of hours on it but can't even achieve step 1 (I thought Power BI was meant to be easy :-()

    Any help would be greatly appreciated.

    Best regards,

    Tim

    Friday, March 4, 2016 12:18 PM

Answers

  • Hi Greg,

    Thank you for your response.  

    I tried to include a link to the file but it prevented with a message along the lines of links not being able to be used by a non-validated member (although I'd responded to the confirmation email).

    I've since worked out there a few things needed to sum the values of a column (PickAmt) for rows that have the same value in one or more columns (State, SKU):

    1. Requires Earlier() function

    2. Earlier() mostly only works with calculated columns and not measures

    3. Filter(All()) are needed when trying to use Earlier() in this way

    Here's the formula that worked (in a calculated column): =CALCULATE(SUM([PickAmt]), FILTER(ALL(Orders), Orders[SKU]=EARLIER(Orders[SKU]) && Orders[State]=EARLIER(Orders[State])))

    Cheers,

    Tim



    • Proposed as answer by Charlie Liao Thursday, March 17, 2016 9:36 AM
    • Marked as answer by TimNoosa Thursday, March 17, 2016 11:09 AM
    Tuesday, March 8, 2016 7:02 AM

All replies

  • Tough to answer this without some sort of context, and by that I mean example data.

    Assuming that you have a table as you specify like:

    SKU,PickAmt,Store,State

    Then, you could achieve one by creating a custom measure:

    MySum= SUM([PickAmt])

    Create a matrix visualization with State,SKU and MySum.

    At this point, completely lost because I have no reference for "SOH of warehouse".

    If you supply example data and desired output, there is a solution.

    • Proposed as answer by Charlie Liao Monday, March 7, 2016 4:09 AM
    Sunday, March 6, 2016 9:13 PM
  • Hi Greg,

    Thank you for your response.  

    I tried to include a link to the file but it prevented with a message along the lines of links not being able to be used by a non-validated member (although I'd responded to the confirmation email).

    I've since worked out there a few things needed to sum the values of a column (PickAmt) for rows that have the same value in one or more columns (State, SKU):

    1. Requires Earlier() function

    2. Earlier() mostly only works with calculated columns and not measures

    3. Filter(All()) are needed when trying to use Earlier() in this way

    Here's the formula that worked (in a calculated column): =CALCULATE(SUM([PickAmt]), FILTER(ALL(Orders), Orders[SKU]=EARLIER(Orders[SKU]) && Orders[State]=EARLIER(Orders[State])))

    Cheers,

    Tim



    • Proposed as answer by Charlie Liao Thursday, March 17, 2016 9:36 AM
    • Marked as answer by TimNoosa Thursday, March 17, 2016 11:09 AM
    Tuesday, March 8, 2016 7:02 AM