DAX coalesce function consumes all available memory

[vgeldbr]

I found what seems to be the correct answer to my conditional formatting challenge in the thread https://community.powerbi.com/t5/Desktop/Conditional-Formatting-Bug/m-p/1127253#M514277 from @edhans . However when I try to use the function "coalesce ( sum ('table'[column]),0)" I get an error saying there is insufficient memory. I can watch in the Windows resource monitor as the memory consumption climbs steadily to the point of failure. The table I'm using has only about 300k rows and the column has currency data. Using the standard sum ( 'table'[column]) works just fine and in seconds. Any ideas what the issue might be?

[edhans · Accepted solution]

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

[vgeldbr]

Thanks for all of the help. I obviously realized I could just create a calculated column in the Engagement Code table but wanted to avoid the large amount of redundant data that would create. I'm still having all sorts of issues with the data model but your help has moved me forward (along with some of my understanding).

[edhans]

Great @vgeldbr - yeah, I avoid calculated columns at all costs. This was done with a Power Query merge, but there, or a custom column in Power Query, or your source data, is much better practice. Glad I was able to help.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

[edhans]

That is because by default, a blank result doesn't show up in the data, which is generally what you want. Everythign is showing up because you've told it "if there is a blank, give me a 0" and zeros do show up. I'm not sure that is what you want, but without data and expected results to test against, it is hard to say.

In the thread you linked to where I used COALESCE as a solution, the user didn't want any blanks, so COALESCE was a good solution there.

[vgeldbr]

Thanks @MattAllington  and @edhans  for help so far. I've simplified this and am totally stuck. I've attached the simplified file which I hope demonstrates the issue.

See Power BI Desktop File

[edhans]

This 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 

[Anonymous]

hi @edhans 

COALESCE does not seem to work with my CALCULATE measure. Can you help?

[Anonymous]

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. 

[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.

[vgeldbr]

@edhans the screenshot below is what I expect to see/need to see:

For each engagement code there is only one line with the sum of all Actuals for that engagement code. Each engagement code could potentially have one of about 10 Engagement Statuses. These can be boiled down to 4 useful Aggregate Statuses from the "dim_Engagement Code Status" table.

[MattAllington]

Why do you need coalesce?  Power BI compresses data and can sum the columns while compressed. My guess is that your formula is executed by the formula engine instead of the storage engine forcing the data to be uncompressed first

[vgeldbr]

@MattAllington see https://community.powerbi.com/t5/Desktop/Conditional-Formatting-Bug/m-p/1127253#M514277 for reason I need coalesce.

[MattAllington]

Try sum('table'[column]) +0

[MattAllington]

Then it is most likely something related to the way you have your columns in the visual. Can you post an image of the model, details of the columns in the visual and also any formulas involved in conditional formatting. 

[vgeldbr]

Thanks for the help @MattAllington . I've simplified down the the aspects that seem to influence this.

Context as described above. Note that Show Items with no Data is checked as this is required.

Data Model:

I have filtered via a slicer the below to use only a single "Engagement Name" to keep things simpler and not so slow.

Table when using SUM('ITRDB Portfolio Actuals'[ExpenseUSDCurrentFYTD]):

Table when using SUM('ITRDB Portfolio Actuals'[ExpenseUSDCurrentFYTD])+0:

The dim_AggregateCodeStatus is used to simplify multiple types of Engagement Code Status values down to 4. This is where the core issue seems to lie but I have absolutely no idea why. 

UPDATE: Conditional formatting is on the Aggregate Status column and uses Field Value and a Status Colour column from dim_EngagementCodeStatus table which is the appropriate colour.

[edhans]

Hi @vgeldbr - the link you provided where I am using COALESCE is a different scenario, and has nothing to do with your memory usage. Coalesce is essientially doing this:

Sample Measure =
IF(
    ISBLANK( [Some Measure] ),
    0,
    [Some Measure]
)

It isn't causing any memory issues. COALESCE is no faster than the IF() statement above, but it is easier to read, and you can do multiple comparisons without doing nested IF() or IF/OR combinations. COALESCE([Measure1],[Measure2],[Measure3],0) for example.

It is hard to say what is happening here, but the first thing I'd do in troubleshooting is get rid of that bidirectional filter. I'd probalby need a copy of the PBIX to play with and see waht is going on. It is difficult to mock up your data and model here with no data.

[vgeldbr]

Thanks @edhans . Based on what @MattAllington  indicated I've come to the same conclusion. My focus now is why my filtering does not work as expected. Even with having gotten rid of the bidirectional filter at your suggestion, if I use the coalesce technique (or just measure+0) then I see ALL of the Aggregate Status values for each Engagement Name (ie. 4 plus a blank). The Engagement Status is "C" so should only show "Closed". I am NOT using the "Show items wtih no data" option in above test. 

If I remove the Aggregate Status column then I get only one row - but I need to show the aggregate status not the many different types of detailed statuses.

[vgeldbr]

Does exactly the same as coalesce and runs out of memory.