Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
Then I have some additional clues:
So, I tried to rebuild the model based on my guess, and changes includes:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |