Forum Discussion

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
5 years ago
Solved

DAX 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 . ...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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

     

     

     

     

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
5 years ago

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

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

    • vgeldbr's avatar
      vgeldbr
      Icon for Helper IV rankHelper IV
      5 years ago

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

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        vgeldbr
        Icon for Helper IV rankHelper IV
        5 years ago


        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.