Strange evaluation context behavior on a calculated table using UNION()

[ryansh]

I have a data model consist of two dimension table and one fact table as below.

The fact table is consolidated from various ERP account receivable aging reports. Each open AR item is identifed by KEY column, and has several snapshot records identified by KEY adn Status Date column. When the open AR is fully cleared, it will be no corresponding record from data source, so I have to create an calculated table to insert the last status.

FT_AR_Collection = 
UNION (
    FILTER ( 'FT_AR Aging', [USD Amount Closed Next Status Day] = 0 ),
    SELECTCOLUMNS (
        FILTER ( 'FT_AR Aging', [USD Amount Closed Next Status Day] <> 0 ),
        "Status Date", [Next Status Date],
        "ERP", [ERP],
        "Company Code", [Company Code],
        "Customer No", [Customer No],
        "Customer Name", [Customer Name],
        "Billing Doc No", [Billing Doc No],
        "Overdue Days", [Overdue Days] + DATEDIFF ( [Status Date], [Next Status Date], DAY ),
        "Due Date", [Due Date],
        "Key", [Key],
        "Prev Status Date", [Status Date],
        "Next Status Date", BLANK (),
        "First Status Date", FALSE (),
        "USD Amount Collected Curr. Day", [USD Amount Closed Next Status Day],
        "USD Amount Closed Next Status Day", BLANK (),
        "Prev USD Amount incd VAT",[USD Amount incd VAT]
    )
)

And create additonal reltaionship with FT_AR_Collection, the data model looks as below

Then I create a calculated measure to evaluate the percentage collected today as,

Pct Collected = DIVIDE(SUM('FT_AR Aging'[Prev USD Amount incd VAT]),
                       SUM('FT_AR Aging'[USD Amount Collected Curr. Day]))

 Then the result of this measure looks like below,

Strage, I don't see any error in the measure's formular, but it does not work as expected.

Even more strange is, if i removed relationship of original table as below,

the filter context dispeared totally,

 I also tried to build calculated column on FT_AR_Collection, like

tPct Collected = DIVIDE(FT_AR_Collection[Prev USD Amount incd VAT],
                         FT_AR_Collection[USD Amount Collected Curr. Day])

The result seems row context does not work neither.

My question is what is expected behaviour of evaluation context and relationship for calculated table, or may be those function involved, (UNION, SELECTCOLUMNS, FILTER). Is this is a bug? Any workaround?

I use Version: 2.43.4647.541 64-bit (February, 2017).

Thanks

[v-sihou-msft]

@ryansh

Does it work properly when only having two dimensions connecting FT_AR Aging table? Can you verify the data in FT_AR_Collection table? 

In this scenario, make sure your columns order are same in both tables when using UNION() to combine them. And based on your Excel source table, it seems there's no fact data for those ARs under that selected company code.

If this issue still persists, can share your .pbix with some sample data?

[ryansh]

To answer you question:

• FT_AR_Collection table has data, but only the first part of UNION() has data, due to data sample, the second part of UNION() returns null
• The filtering only works for the two columns from the dimension table (not from the FT_AR_Collection table). Filtering on additional columns from FT_AR_Collection does not work

Then I have some additional clues:

• I f ound that the second table of the UNION() returns null in my sample data, and thus might cause the engine do some unwanted optimization behaviour. This is just my guess.
• The dimension filtering behaviour only works for the column of the direct result table of the UNION(), but not work for any calculated metrics or column based on the resulting table. This is where the optimization failed, I guess

So, I tried to rebuild the model based on my guess, and changes includes:

• I use calculatede table to save the intermediate resulting table
• I get a batch of more real test data, and both intermieidate table is not null

Then, the filtering works as expected.

[ryansh]

I am sure my guess that the strange behavior is related to one part of UNION() is null.

I finalized my model, without intermediate table, it works as expected.

FT_AR_Collection = 
UNION (
    SELECTCOLUMNS (
        FILTER ( 'FT_AR Aging', [Local Amount Closed Next Status Day] = 0 ),
        "Status Date", [Status Date],
        "ERP", [ERP],
        "Company Code", [Company Code],
        "Customer No", [Customer No],
        "Customer Name", [Customer Name],
        "Billing Doc No", [Billing Doc No],
        "Document Date", [Document Date],
        "Currency", [Currency],
        "Overdue Days", [Overdue Days],
        "Document Amount incd VAT", [Document Amount incd VAT],
        "Local Amount incd VAT", [Local Amount incd VAT],
        "Document VAT Amount", [Document VAT Amount],
        "Document Amount excd VAT", [Document Amount excd VAT],
        "Due Date", [Due Date],
        "Payment Date", [Payment Date],
        "Current", [Current],
        "0-29 Days", [0-29 Days],
        "30-59 Days", [30-59 Days],
        "60-89 Days", [60-89 Days],
        "90-119 Days", [90-119 Days],
        "120-179 Days", [120-179 Days],
        "180-359 Days", [180-359 Days],
        "Over 360 Days", [Over 360 Days],
        "Invoice Amount Original Currency", [Invoice Amount Original Currency],
        "Paid Amount Original Currency", [Paid Amount Original Currency],
        "VAT Due", [VAT Due],
        "Item Text", [Item Text],
        "InvoicePaymentTerm", [InvoicePaymentTerm],
        "InvPayment TermDescr", [InvPayment TermDescr],
        "Profit Center/Location", [Profit Center/Location],
        "Location Descr", [Location Descr],
        "Sales Document", [Sales Document],
        "Sales Document Text", [Sales Document Text],
        "Project No", [Project No],
        "Project Description", [Project Description],
        "Service Order", [Service Order],
        "Service Description", [Service Description],
        "Service Site Name", [Service Site Name],
        "WBS Element", [WBS Element],
        "PO Number", [PO Number],
        "Contact Person", [Contact Person],
        "Business Area", [Business Area],
        "Team", [Team],
        "LOB", [LOB],
        "Sales Group", [Sales Group],
        "Sales Office", [Sales Office],
        "Division", [Division],
        "Distribution", [Distribution],
        "Credit Split %", [Credit Split %],
        "Job Allocation", [Job Allocation],
        "Fiscal Year", [Fiscal Year],
        "G/L Account", [G/L Account],
        "A/C Document", [A/C Document],
        "Document Type", [Document Type],
        "Document Header Text", [Document Header Text],
        "A/C Document Item", [A/C Document Item],
        "Posting Date", [Posting Date],
        "Inter Company Code", [Inter Company Code],
        "User ID", [User ID],
        "Branch Customer", [Branch Customer],
        "Customer Type Descr", [Customer Type Descr],
        "Credit Limit", [Credit Limit],
        "DELIVBLK", [DELIVBLK],
        "Local Currency", [Local Currency],
        "Cust Paym Term Code", [Cust Paym Term Code],
        "Key", [Key],
        "Prev Status Date", [Prev Status Date],
        "Next Status Date", [Next Status Date],
        "Local Amount Collected Curr. Day", [Local Amount Collected Curr. Day],
        "Prev Local Amount incd VAT", [Prev Local Amount incd VAT],
        "FXRateKey", [FXRateKey],
        "Prev Document Amount incd VAT", [Prev Document Amount incd VAT],
        "Document Amount Collected Curr. Day", [Document Amount Collected Curr. Day]
    ),
    SELECTCOLUMNS (
        FILTER ( 'FT_AR Aging', [Local Amount Closed Next Status Day] <> 0 ),
        "Status Date", [Status Date],
        "ERP", [ERP],
        "Company Code", [Company Code],
        "Customer No", [Customer No],
        "Customer Name", [Customer Name],
        "Billing Doc No", [Billing Doc No],
        "Document Date", [Document Date],
        "Currency", [Currency],
        "Overdue Days", [Overdue Days],
        "Document Amount incd VAT", [Document Amount incd VAT],
        "Local Amount incd VAT", [Local Amount incd VAT],
        "Document VAT Amount", [Document VAT Amount],
        "Document Amount excd VAT", [Document Amount excd VAT],
        "Due Date", [Due Date],
        "Payment Date", [Payment Date],
        "Current", [Current],
        "0-29 Days", [0-29 Days],
        "30-59 Days", [30-59 Days],
        "60-89 Days", [60-89 Days],
        "90-119 Days", [90-119 Days],
        "120-179 Days", [120-179 Days],
        "180-359 Days", [180-359 Days],
        "Over 360 Days", [Over 360 Days],
        "Invoice Amount Original Currency", [Invoice Amount Original Currency],
        "Paid Amount Original Currency", [Paid Amount Original Currency],
        "VAT Due", [VAT Due],
        "Item Text", [Item Text],
        "InvoicePaymentTerm", [InvoicePaymentTerm],
        "InvPayment TermDescr", [InvPayment TermDescr],
        "Profit Center/Location", [Profit Center/Location],
        "Location Descr", [Location Descr],
        "Sales Document", [Sales Document],
        "Sales Document Text", [Sales Document Text],
        "Project No", [Project No],
        "Project Description", [Project Description],
        "Service Order", [Service Order],
        "Service Description", [Service Description],
        "Service Site Name", [Service Site Name],
        "WBS Element", [WBS Element],
        "PO Number", [PO Number],
        "Contact Person", [Contact Person],
        "Business Area", [Business Area],
        "Team", [Team],
        "LOB", [LOB],
        "Sales Group", [Sales Group],
        "Sales Office", [Sales Office],
        "Division", [Division],
        "Distribution", [Distribution],
        "Credit Split %", [Credit Split %],
        "Job Allocation", [Job Allocation],
        "Fiscal Year", [Fiscal Year],
        "G/L Account", [G/L Account],
        "A/C Document", [A/C Document],
        "Document Type", [Document Type],
        "Document Header Text", [Document Header Text],
        "A/C Document Item", [A/C Document Item],
        "Posting Date", [Posting Date],
        "Inter Company Code", [Inter Company Code],
        "User ID", [User ID],
        "Branch Customer", [Branch Customer],
        "Customer Type Descr", [Customer Type Descr],
        "Credit Limit", [Credit Limit],
        "DELIVBLK", [DELIVBLK],
        "Local Currency", [Local Currency],
        "Cust Paym Term Code", [Cust Paym Term Code],
        "Key", [Key],
        "Prev Status Date", [Prev Status Date],
        "Next Status Date", [Next Status Date],
        "Local Amount Collected Curr. Day", [Local Amount Collected Curr. Day],
        "Prev Local Amount incd VAT", [Prev Local Amount incd VAT],
        "FXRateKey", [FXRateKey],
        "Prev Document Amount incd VAT", [Prev Document Amount incd VAT],
        "Document Amount Collected Curr. Day", [Document Amount Collected Curr. Day]
    ),
    SELECTCOLUMNS (
        FILTER ( 'FT_AR Aging', [Local Amount Closed Next Status Day] <> 0 ),
        "Status Date", [Next Status Date],
        "ERP", [ERP],
        "Company Code", [Company Code],
        "Customer No", [Customer No],
        "Customer Name", [Customer Name],
        "Billing Doc No", [Billing Doc No],
        "Document Date", [Document Date],
        "Currency", [Currency],
        "Overdue Days", [Overdue Days] + DATEDIFF ( [Status Date], [Next Status Date], DAY ),
        "Document Amount incd VAT", 0,
        "Local Amount incd VAT", 0,
        "Document VAT Amount", 0,
        "Document Amount excd VAT", 0,
        "Due Date", [Due Date],
        "Payment Date", [Payment Date],
        "Current", 0,
        "0-29 Days", 0,
        "30-59 Days", 0,
        "60-89 Days", 0,
        "90-119 Days", 0,
        "120-179 Days", 0,
        "180-359 Days", 0,
        "Over 360 Days", 0,
        "Invoice Amount Original Currency", [Invoice Amount Original Currency],
        "Paid Amount Original Currency", [Invoice Amount Original Currency],
        "VAT Due", 0,
        "Item Text", [Item Text],
        "InvoicePaymentTerm", [InvoicePaymentTerm],
        "InvPayment TermDescr", [InvPayment TermDescr],
        "Profit Center/Location", [Profit Center/Location],
        "Location Descr", [Location Descr],
        "Sales Document", [Sales Document],
        "Sales Document Text", [Sales Document Text],
        "Project No", [Project No],
        "Project Description", [Project Description],
        "Service Order", [Service Order],
        "Service Description", [Service Description],
        "Service Site Name", [Service Site Name],
        "WBS Element", [WBS Element],
        "PO Number", [PO Number],
        "Contact Person", [Contact Person],
        "Business Area", [Business Area],
        "Team", [Team],
        "LOB", [LOB],
        "Sales Group", [Sales Group],
        "Sales Office", [Sales Office],
        "Division", [Division],
        "Distribution", [Distribution],
        "Credit Split %", [Credit Split %],
        "Job Allocation", [Job Allocation],
        "Fiscal Year", "",
        "G/L Account", "",
        "A/C Document", "",
        "Document Type", "",
        "Document Header Text", "",
        "A/C Document Item", "",
        "Posting Date", "",
        "Inter Company Code", "",
        "User ID", "",
        "Branch Customer", [Branch Customer],
        "Customer Type Descr", [Customer Type Descr],
        "Credit Limit", [Credit Limit],
        "DELIVBLK", [DELIVBLK],
        "Local Currency", [Local Currency],
        "Cust Paym Term Code", [Cust Paym Term Code],
        "Key", [Key],
        "Prev Status Date", [Status Date],
        "Next Status Date", BLANK (),
        "Local Amount Collected Curr. Day", [Local Amount Closed Next Status Day],
        "Prev Local Amount incd VAT", [Local Amount incd VAT],
        "FXRateKey", IF (
            MONTH ( [Next Status Date] ) = 1,
            FORMAT ( YEAR ( [Next Status Date] ) - 1, "0000" )
                & "01",
            FORMAT ( YEAR ( [Next Status Date] ), "0000" )
                & FORMAT ( MONTH ( [Next Status Date] ) - 1, "00" )
        )
            & [Local Currency]
            & "USD",
        "Prev Document Amount incd VAT", [Document Amount incd VAT],
        "Document Amount Collected Curr. Day", [Document Amount Closed Next Status Day]
    )
)

Begining Open AR Amt. LC. = 
VAR FirstStatusDate =
    MIN ( FT_AR_Collection[Status Date] )
RETURN
    IF (
        HASONEVALUE ( FT_AR_Collection[Local Currency] ),
        CALCULATE (
            SUM ( FT_AR_Collection[Local Amount incd VAT] )
                + SUM ( FT_AR_Collection[Local Amount Collected Curr. Day] ),
            FT_AR_Collection[Status Date] = FirstStatusDate
        ),
        BLANK ()
    )

This strange behaviour should be considered as a bug, because there would be such data situation.