PowerPivot measure returning an expression is working while returning a variable result is not

[JoeKang]

Hi guys, 

I must have missed something with below two measures. My power pivot data model is set up to go from invoices to invoice lines and then down to good receipts. I wanted to calculated a bunch of days, one of which is the duration of invoice DueDate to InvoiceDate. The combination of InvoiceKey ( invoice line number ) and receipt date will guarantee one unique line in the report matrix. For the grand total row, I just wanted to show '***' instead of a weighted average duration. To that end, I can place the first measure into the report matrix while placing in the second takes forever. This puzzles me and I have no clue of what's going on. I would appreciate if you could take a look and direct me where I go wrong with the second measure.  

Days_WorksJustFine:=
VAR FilteredReceipts = Receipts
VAR NumFilteredReceipts = COUNTROWS( FilteredReceipts )
VAR Days = SUMX( FilteredReceipts , INT( RELATED( Invoices[DueDate]) - RELATED( Invoices[InvoiceDate]) ) )
RETURN IF( NumFilteredReceipts >1, "***", Days )

Days_CalculatesForever:=
VAR FilteredReceipts = Receipts

VAR NumFilteredReceipts = COUNTROWS( FilteredReceipts )

VAR Days = SUMX( FilteredReceipts , INT( RELATED( Invoices[DueDate]) - RELATED( Invoices[InvoiceDate]) ) )

VAR Result = IF( NumFilteredReceipts >1, "***", Days )

RETURN Result

Thanks!

[v-jingzhang]

Hi @JoeKang 

I don't know why the second measure doesn't work well. I usually use HASONEVALUE function to deal with such scenarios. For example

Measure =
VAR Days =
    SUMX (
        Receipts,
        INT ( RELATED ( Invoices[DueDate] ) - RELATED ( Invoices[InvoiceDate] ) )
    )
RETURN
    IF ( HASONEVALUE ( Receipts[Receipt] ), Days, "***" )

Maybe you can try if this measure works?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JoeKang]

Hi Jing,  

Thank you for looking into this!
The reason that I did not use HasOneValue is because I know I will need to use the same Receipts table under current filter context to calculate my final average days. So I used a variable to hold the filtered reciept table. HasOneValue requires a fully qualified column name from base table. 
For my test model, I was wondering it could be due to the case that the cartesion product of pairs was huge ( Unique invoices: 49K rows * unique InvoiceLines: 50K rows * unique Receipts: 49K rows * Vendors: 1.1K). 
In my production model, I denomalized my data tables and turned the schema from a snowflake to a standard star one, which has brought the number of pairs down to 54 million ( Receipts: 49K *  Vendors: 1.1K ) - this is because most of the transactions are of one-invoice-one-invoiceline-one-receipt. I tested the second measure in my production model, still it does not work.
Nevertherless, the measure in my production model calculating the weighted averge days, I used a variable result as the return and it works just fine. 
I cannot replicate the issue with AdventureWork table by calculateing the duration from OrderDay to DeliveryDate. So there must be something wrong there in my model.

DAYS_InvoiceDateToDueDate:=
VAR FilteredReceipts = Receipts
VAR DayWeightedInvoiceAmount = 
    SUMX( FilteredReceipts, 
        IF( ISBLANK( Receipts[InvoiceDueDate] ), 
            BLANK(), 
            MAX( ( Receipts[InvoiceDueDate] - Receipts[InvoiceDate] ),0) * DIVIDE( Receipts[Amount], Receipts[Quantity]) * Receipts[InvoiceQty] 
        )
    )
VAR TotalInvoiceAmount = 
    SUMX( FilteredReceipts, 
        IF( ISBLANK( Receipts[InvoiceDueDate]), 
            BLANK(), 
            DIVIDE( Receipts[Amount], Receipts[Quantity]) * Receipts[InvoiceQty] 
        ) 
    )
VAR Days = INT( DIVIDE( DayWeightedInvoiceAmount, TotalInvoiceAmount ) )
RETURN Days