Forum Discussion

JoeKang's avatar
JoeKang
Regular Visitor
3 years ago

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

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!

 

Need Help

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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's avatar
      JoeKang
      Regular Visitor
      3 years ago

      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