Forum Discussion

Sergii24's avatar
Sergii24
Super User
5 years ago
Solved

Issue with evaluation context/context transition

Dear all,

 

I've got lost a bit in all types of filters and transitions of Power BI and would appretiate a lot your help!

In my dataset I have columns for Stock and Orders in items that should be delivered. The objective is to create a measure that will evaluate how many  items I can deliver, so the logic is the following: Match = IF (Stock>Orders) then Orders else Stock (example: I have 10 items of product A and orders for 15 items, so Match = 10 (statement IF (10>15) is FALSE).

This calculation should be done on row level, therefore I've created the following Base Measure to calcualte Orders and Stock:

Stock Qty = 
    SUMX( 
        Stock,
        Stock[Stock Qty] 
    )

Open Orders = 
    SUMX( 
        'Open Orders',
        'Open Orders'[Quantity] 
    )

 

As well as the formula for Match Measure:

Match (Stock) = 
VAR _NetStock = [Stock Qty] - [Open Orders]
VAR _Result =
    IF(
        _NetStock >= 0,
        [Open Orders],
        [Stock Qty]
    )
RETURN _Result

 

It works well on the lowest level of the granularity, but fails on aggregations. I expect 20 as the result for Herarchy Level 3, instead the result is 27.

 

Any ideas what do I miss?
Thank you very much!

  • Sergii24 

    Not clear about the model and the relationship. Try an approach like below

     

    Match (Stock) =
    
    SUMX(
        VALUES(Stock[Items]) 
        VAR _NetStock = [Stock Qty] - [Open Orders]
        VAR _Result =
        IF(
            _NetStock >= 0,
            [Open Orders],
            [Stock Qty]
        )
        RETURN _Result
    )

     

     

3 Replies

  • Sergii24 

    Not clear about the model and the relationship. Try an approach like below

     

    Match (Stock) =
    
    SUMX(
        VALUES(Stock[Items]) 
        VAR _NetStock = [Stock Qty] - [Open Orders]
        VAR _Result =
        IF(
            _NetStock >= 0,
            [Open Orders],
            [Stock Qty]
        )
        RETURN _Result
    )

     

     

    • Sergii24's avatar
      Sergii24
      Super User

      Fowmy , thank you very much! It looks like that outer SUMX is what was missing!

      In my understanding once I wrote 2 separate measures for "Stock Qty" and "Open Orders" I was ready to use them together in a new, 3rd measure, having already a row context, which is wrong.
      The correct way, instead, is to have another outer SUMX even in my third variable ("Match Stock") that is introducing row context for this new measure for the first time!

      Thanks a lot for filling this gap in my knowledge! 🙂

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Can you please provide a sample dataset or PBIX file? Thanks!