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 ChampionThat 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.
vgeldbrThanks 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
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
- 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.