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

gwright15's avatar
gwright15
Icon for Helper I rankHelper I
7 years ago

Sorry to post on an old thread, but i'm using a similar dax formula.  Whilst working while, it is very slow - around 22000ms.

 

Calculate (
    Sum(Deals[Value]),
    USERELATIONSHIP('Date'[Date],deals[Add_Time_Local]),
    USERELATIONSHIP(activities[assigned_to_user_id],Pipedrive_Users[id]),
    CROSSFILTER(activities[deal_id],deals[id],Both),
    Filter(deals,deals[id] In VALUES(activities[deal_id])),
    NOT(deals[status]="deleted"

)

    )

For context, the activities table is circa 450k rows and deals table circa 80k rows.  One deal can be multi activties.

 

The formula is looking where an activtiy existing, what is the deal value.