Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX help - filter another table based on value in current row

I have two fact tables, one with info about Purchases, and the other with info about Purchases Forecast. Here's a simplified look at the data model:     And here's a sample of data for the P...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    Please check if below measures match your request.Attached the file for your reference.

    PurchasesQuantity =
CALCULATE (
    SUM ( Purchases[Quantity] ),
    FILTER (
        'Purchases',
        Purchases[Reception No] IN VALUES ( 'Purchases Forecast'[Reception No] )
    )
)

    ForecastQuantity =
CALCULATE (
    SUM ( 'Purchases Forecast'[Forecast Quantity] ),
    USERELATIONSHIP ( 'Purchases Forecast'[Item], 'Item'[Item No] )
)

    Diff =
SUMX (
    Purchases,
    ABS ( 'Purchases Forecast'[ForecastQuantity] - Purchases[PurchasesQuantity] )
)

    Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous

 

Please check if below measures match your request.Attached the file for your reference.

PurchasesQuantity =
CALCULATE (
    SUM ( Purchases[Quantity] ),
    FILTER (
        'Purchases',
        Purchases[Reception No] IN VALUES ( 'Purchases Forecast'[Reception No] )
    )
)

ForecastQuantity =
CALCULATE (
    SUM ( 'Purchases Forecast'[Forecast Quantity] ),
    USERELATIONSHIP ( 'Purchases Forecast'[Item], 'Item'[Item No] )
)

Diff =
SUMX (
    Purchases,
    ABS ( 'Purchases Forecast'[ForecastQuantity] - Purchases[PurchasesQuantity] )
)

Regards,

Cherie

PhilDanley's avatar
PhilDanley
Icon for Advocate I rankAdvocate I
4 years ago

Thank you Cherie. This helped me write the correct measure to filter one table based upon values in a second table!