Forum Discussion
Helper IVDAX coalesce function consumes all available memory
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?
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
18 Replies
- MattAllington
Community Champion
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
vgeldbrMattAllington 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
- 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.
- edhans
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
