DAX formula working in Power BI but not on Power Pivot

0
votes

The following DAX formula works on Power BI but not on Power Pivot (don't show error but blank cells) using the same exact data model (checked this many times).

It's used in a calculated column.

Actual Units SO = 
CALCULATE (
    SUM ( COM_SellOut[QuantitySold] ),
    FILTER ( COM_SellOut, COM_SellOut[Date] >= TMK_Promotion[FromDate] ),
    FILTER ( COM_SellOut, COM_SellOut[Date] <= TMK_Promotion[ToDate] ),
    FILTER ( COM_SellOut, COM_SellOut[ProductCode] = TMK_Promotion[ProductCode] ),
    FILTER ( COM_Customers, COM_Customers[id] = TMK_Promotion[Customer] )
)

A little context: this formula iterates a promotion table and calculates the units sold of a given product, in a given customer between a given period. Works flawlessly in PowerBI but not on PowerPivot.

Any ideas of what could be causing the problem?

Thanks in advance!

2
excelpowerbidaxpowerpivot
Is there a reason for using 4 separate FILTER functions rather than either nesting them or concatenating the conditions in to one criterion using && in one FILTER? – Rory
No, just did this as a first version but can implement the && in one FILTER, no problem. – Francisco Palumbo
I'd start there, and you may need to add a RELATED to each reference to the other table (presumably TMK_Promotion) – Rory
Ok I'll try that first and come back with the results. If the goal is to modify the filter context for each row on the iteration, why REPLACE would be needed if i'm not looking for a value in the other tables? – Francisco Palumbo
It's a calculated column, so it's row context, not filter context and you are trying to relate it to a value in another table as far as I can see. Which table is this calculation in? – Rory

2 Answers

1
votes

Power Pivot and Power BI use the same engine, so the same version DAX works in both applications. Are you using a colon (Actual Units SO :=) in front of your equal sign (no space). In Power Pivot it's a must whereas in Power BI it's optional. I see in your example there is no colon, so your DAX measure won't work in Power Pivot. It's a syntax issue not a model or DAX issue.

Actual Units SO :=    
    CALCULATE (
        SUM ( COM_SellOut[QuantitySold] ),
        FILTER ( COM_SellOut, COM_SellOut[Date] >= TMK_Promotion[FromDate] ),
        FILTER ( COM_SellOut, COM_SellOut[Date] <= TMK_Promotion[ToDate] ),
        FILTER ( COM_SellOut, COM_SellOut[ProductCode] = TMK_Promotion[ProductCode] ),
        FILTER ( COM_Customers, COM_Customers[id] = TMK_Promotion[Customer] )
    )
1
votes

I think you can get a better performance by using logical and in one filter rather than nesting your filters :

Actual Units SO = 
CALCULATE (
    SUM ( COM_SellOut[QuantitySold] ),
    FILTER ( COM_SellOut, COM_SellOut[Date] >= TMK_Promotion[FromDate] 
        && COM_SellOut, COM_SellOut[Date] <= TMK_Promotion[ToDate]
        && COM_SellOut, COM_SellOut[ProductCode] = TMK_Promotion[ProductCode]
        &&  COM_Customers, COM_Customers[id] = TMK_Promotion[Customer]
      )   
)