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 ChampionHi 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.
Helper IVThanks 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.