Forum Discussion
Helper IVDAX coalesce function consumes all available memory
Ok, vgeldbr - see if this helps. I think this is a modeling problem. Your aggregate status was another table in a 1:Many relationship with the Engagement Codes table, and the latter wasn't filtering the former.
I could have worked on some DAX to do some crossfiltering, but that is more complex than it needs to be. The Aggregate status is really just a lookup table it seems, so I just merged it into the Engagement table. You can see my results in the lower right.
This is a super simple measure.
Actuals = VAR varActualCount = COUNTROWS(Actuals) RETURN IF( ISBLANK(varActualCount), 0, SUM(Actuals[ExpenseUSDCurrentFYTD]) )If there are no rows in the Actuals for a given code, return zero, otherwise the total. At this point, your dim_engagement codes doesn't even need to be loaded. Use your Engagement codes as the DIM table and the Actuals as a FACT table - a perfect Star Schema. See my PBIX file here. Look at the merge I did in Power Query.
Microsoft Guidance on Importance of Star Schema
Community ChampionThis measure returns the following results:
Actuals =
COALESCE(
SUM(Actuals[ExpenseUSDCurrentFYTD]),
0
)
If that is not what you need, please provide an example (Excel screenshot is ok) of what you expect the results to be vgeldbr
- edhans
You should start a new thread as this one has been marked solved.
But COALESCE has nothing to do with it. You are iterating over the entire DIM table because of the ALL() table, then doing some IF logic, which isn't a terribly efficient function, and that is creating a table and then applying that to a measure that I have no idea what it is doing.
The general rule is, filter columns, not tables. I would start by replacing the table in ALL with just the quarter reporting field as it seems that is all you need.
But again, a new thread, as this goes beyond COALESCE. And include the contents of your measure you are using modifying.How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables. hi edhans
Thank you for the response.
Just to close the loop here.
My desired outcome with the use of COALESCE was to show zero value where the measure yielded no result.
The measure does not feel very efficient indeed, but I don't have an alternative at the moment. The measure serves as time intelligence (corresponding value last quarter last year) in my model in the absence of not being able to use a date table. Due to three reasons: 1) our clients have mulitple companies/subsidiaries in the same database and 2) the reporting/accounting periods for each company is different eg the reporting month for company one for Jan 2023 is from 25 Jan - 23 Feb and for company two is 23 Jan - 21 Feb ; and 3) the reporting period month on month also differs eg company one for Jan 2023 is from 25 Jan - 23 Feb and for Feb 2023 is from 24 Feb - 22 Mar.
Thus because each company has different reporting periods my date dimension table wont have one row per date and I cant mark it as a date table. Hence my inefficient DAX that can use standard time intelligence functions.