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.
Hi there,
I've tried everything, & I'm at the end of my wits with this SSAS Tabular Model project. I think I'm getting accurate results now, but it's extremely slow and I can't pin down why. Any help would be immensely appreciated!
Here's a link to a Power BI .pbix file with sample data:
drive.google.com/file/d/128rvGqMmCFjvAUBMmr9x06Z1fjStihJW/view?usp=sharing
High-level, we need to get a Document's "Outstanding Balance", convert it to USD, then filter into an Aging Bucket. Here are the detailed steps:
Calculate the Outstanding Balance for a Document in the functional currency
Find the appropriate exchange rate & convert the amount to USD
Place the resulting amount into an "Aging Bucket" (e.g. 15-30 Days, 90+ Days, etc)
Dynamically alter all of the above based on an "As Of Date" table filtered by the user
Measure:
Outstanding Balance (USD) =
VAR AsOfDate = MIN( MAX('xAs Of Date'[As Of Date]), TODAY() ) // Earlier of [the latest As Of Date in the original filter context] or [Today]. Use MAX so it still works when rolled up to year or when there is no filter applied
VAR AsOfMonth = CALCULATE(
MAX( 'xAs Of Date'[Month Start Int - As Of] )
, 'xAs Of Date'[As Of Date] = AsOfDate
)
VAR AsOfDates = CALCULATETABLE(
VALUES('xAs Of Date'[As Of Date])
// The Collections table structure requires that we SUM all prior transactions to get the Outstanding Balance for a Document
// So we must override the As Of Date filter to include all dates before the most recent As Of Date, defined in the MaxDate variable above
// Otherwise, we would only get transaction amounts within that specific date range
, 'xAs Of Date'[As Of Date] <= AsOfDate
)
VAR Aged = FILTER( // Filter to dates within the Aging Bucket, based on the Due Date in relation to the As Of Date
VALUES('xDate - Document Due'[Document Due Date]),
VAR DaysOD = DATEDIFF( 'xDate - Document Due'[Document Due Date], AsOfDate, DAY )
RETURN COUNTROWS( // Second FILTER argument must be a Boolean value
FILTER( 'Aging Buckets'
, SELECTEDVALUE('Aging Buckets'[Lower Bound]) <= DaysOD && DaysOD <= SELECTEDVALUE('Aging Buckets'[Upper Bound])
)
) > 0
)
VAR Docs = CALCULATETABLE(
VALUES(Document[Document Key])
, KEEPFILTERS(Aged)
// Exclude unnecessary Documents based on the As Of Date filter context
, KEEPFILTERS('xDate - Document Date'[Document Date] <= AsOfDate) // Didnt exist yet
, KEEPFILTERS('xDate - Document Settled'[Document Settled Date] > AsOfDate || ISBLANK('xDate - Document Settled'[Document Settled Date]) ) // Already settled
)
VAR Balance = CALCULATE( // If the currency is already USD, we can avoid the rate calculation below & just use the Functional Amount measure
VAR USD = CALCULATE(
SUM(Collections[Functional Amt])
, KEEPFILTERS(Entity[Entity Currency] = "USD")
)
VAR Intl = CALCULATE(
SUMX( Entity,
VAR FxRate = CALCULATE( // Use Context Transition to find each Entity USD Rate during the iteration
VALUES('USD Consolidation Rate'[USD Consolidation Rate])
, 'USD Consolidation Rate'[Month Start Int] = AsOfMonth
, USERELATIONSHIP( Entity[Entity Key], 'USD Consolidation Rate'[Entity Key] ) // Temporarily activate the inactive relationship
)
RETURN FxRate * CALCULATE( SUM(Collections[Functional Amt]) )
)
, KEEPFILTERS(Entity[Entity Currency] <> "USD")
)
VAR Final = USD + Intl
RETURN Final
, AsOfDates
, KEEPFILTERS(Docs)
)
VAR Final = IF(ROUNDDOWN(ABS(Balance), 0) = 0, BLANK(), Balance) // Hide 0 Balances & pennies remaining from bad GP data
RETURN Final
Relationships:
I'm completely stuck so thank you so much in advance for any help!
So I might have made some progress. It appears changing Entity[Entity Key] here to use the SUMMARIZE instead makes most calculations go faster.
Does anyone know how to explain why?
I'm still a bit murky on expanded tables and data lineage. My best guess is that the Entity table can't access Customer, Document, or other dimensions, which caused cross joins or some other inefficiency.
I also updated the .pbix file: drive.google.com/file/d/128rvGqMmCFjvAUBMmr9x06Z1fjStihJW/view?usp=sharing
Question: Why can't you just pre-calculate all balances for all accounts for all days you're interested in? Then your measure would be dead simple and lightning fast, as would be the converted amount. But if you try to calculate the balances for every day and every account (and what have you) on the fly... well, that may cost you a lot.
I'm not a fan of the above DAX, either 🙂 It's too complex to say the least. I'm sure "There MUST be a better way," to paraphrase Raymond Hettinger.
After looking into this, I'm actually a little confused on how to best set this up. Can you elaborate on what you're thinking?
I created a Calculated Column in Collections to store the Rolling Balance. Unfortunately, the measure I wrote actually ended up slower than the original. I still don't know of a way to not need to clear the Collections[As Of Date] filter to get the Outstanding Balance, which likely slows things down.
Am I missing something?
Updated .pbix: drive.google.com/file/d/128rvGqMmCFjvAUBMmr9x06Z1fjStihJW/view?usp=sharing
VAR AsOfDate = MIN( MAX('xAs Of Date'[As Of Date]), TODAY() ) // Earlier of the latest As Of Date in the original filter context or Today. Use MAX so it still works when rolled up to year or when there is no filter applied
VAR LastValue = CALCULATETABLE(
TOPN( 1
, SUMMARIZE(Collections, Collections[Document Key], Collections[From Document Key], Collections[As Of Date])
, Collections[As Of Date], DESC, Collections[From Document Key], DESC
)
, 'xAs Of Date'[As Of Date] <= AsOfDate
/*** This section is surprisingly faster, but still slower than the original measure ***/
// , FILTER( ALL(Collections[As Of Date])
// , Collections[As Of Date] <= AsOfDate
// )
// , CROSSFILTER('xAs Of Date'[As Of Date], Collections[As Of Date], NONE)
)
VAR Balance = CALCULATE(
SUM(Collections[RollingFuncAmt])
, LastValue
, 'xAs Of Date'[As Of Date] <= AsOfDate
// , CROSSFILTER('xAs Of Date'[As Of Date], Collections[As Of Date], NONE)
)
VAR Final = IF(ROUNDDOWN(ABS(Balance), 0) = 0, BLANK(), Balance) // Hide 0 Balances & pennies remaining from bad GP data
RETURN Final
You have to pre-calculate the balances not in DAX but in the source system (SQL Server, right?) or in Power Query. Then and only then will it make a lot of sense.
Not sure what you mean. I calculated it in SQL as well and it's the same as the DAX Calculated Column.
That's definitely something I started to consider lately. I'm going to look into how to structure that today and I'll follow-up here.
I still think there might be efficiency issues outside of that though. If I comment out this section and the reference to it:
VAR Aged = FILTER( // Filter to dates within the Aging Bucket, based on the Due Date in relation to the As Of Date
VALUES('xDate - Document Due'[Document Due Date]),
VAR DaysOD = DATEDIFF( 'xDate - Document Due'[Document Due Date], AsOfDate, DAY )
RETURN COUNTROWS( // Second FILTER argument must be a Boolean value
FILTER( 'Aging Buckets'
, 'Aging Buckets'[Lower Bound] <= DaysOD && DaysOD <= 'Aging Buckets'[Upper Bound]
)
) > 0
)
most things appear to go much faster, which seems odd to me.
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 |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |