Forum Discussion
Strange evaluation context behavior on a calculated table using UNION()
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...
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?
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.
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.