Forum Discussion

Mr_Robot0092's avatar
Mr_Robot0092
Icon for Helper I rankHelper I
11 months ago
Solved

DAX query with SUMMARIZECOLUMNS - Optimization

Hi everyone, I’m facing a critical performance issue in Power BI when running an advanced DAX query used by a paginated report. The query operates over a large fact table (~131 million rows) and pro...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    11 months ago

    Hi Mr_Robot0092,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to amitchandak, for his inputs on this thread.

    Thanks for confirming and for testing that. Just to clarify the syntax issue you are seeing with FILTER() inside SUMMARIZECOLUMNS is expected. The function doesn’t allow filters in that position, which is why it errors out.

    However, even if the syntax was adjusted, it unfortunately wouldn’t resolve the original memory issue. The error you are facing is caused by the size of the intermediate tables this query generates, combined with the full outer join in DAX. That is what triggers the Resource Governance limit.

    The best approach here is still to move the heavy transformations (joins, merging, grouping, filtering) out of DAX and into Power Query / SQL / Warehouse and only return the required dataset to the report. That is what will avoid the memory limitation completely.

    Refer this link: https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax 

    Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

    Thank you for using the Microsoft Fabric Community Forum.

Mr_Robot0092's avatar
Mr_Robot0092
Icon for Helper I rankHelper I
11 months ago

Thanks for replying and suggestions.
For the changes , i have an error when i used the filter inside SUMMARIZECOLUMNS

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
11 months ago

Mr_Robot0092 , Please share the code SUMMARIZECOLUMNS and error

  • Mr_Robot0092's avatar
    Mr_Robot0092
    Icon for Helper I rankHelper I
    11 months ago

    amitchandak sur before i used this code : 

    EVALUATE
//------------------------------------
// DATASET A (View A)
//------------------------------------
VAR A_Base =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'DimDate'[DateKey],
            'DimDate'[Period],
            'DimAttribute1'[CategoryA],
            'DimAttribute2'[CategoryB],
            'DimAttribute3'[CategoryC],
            'DimEntityA'[EntityKey],
            'DimEntityB'[ExternalKey],
            'DimLocationA'[LocationCode],
            'DimLocationA'[DirectionCode],
            'DimGroupA'[GroupLabel],
            'DimEntityA'[EntityName],
            'DimAttribute1'[City],
            'DimAttribute1'[PostalCode],
            'DimStatus'[StatusLabel],
            'DimHierarchyA'[HierarchyLevel],
            'DimEntityA'[EntityType],
            'DimEntityA'[EntitySubtype],
            "MetricA_Main", [MetricA_Main]
        ),
            'DimDate'[Period]          = "2025-11",
            'DimAttribute4'[CategoryA] = "All",
            DimTypeOrder[OrderCode]          IN { 11, 32}
    )

    Your optimization :

    VAR A_Base =
    SUMMARIZECOLUMNS(
        'DimDate'[DateKey],
        'DimDate'[Period],
        ...
        "MetricA_Main", [MetricA_Main],
        FILTER( DimTypeOrder, DimTypeOrder[OrderCode] IN { 11, 32} ),
        FILTER( DimDate, 'DimDate'[Period] = "2025-11" ),
        FILTER( DimAttribute4, 'DimAttribute4'[CategoryA]="All" )
    )

     

    ERROR : The SUMMARIZECOLUMNS function expects a column name as argument number 20.

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      11 months ago

      Mr_Robot0092 , Use all the columns. ... was used to avoid full code typing here

      VAR A_Base =
      SUMMARIZECOLUMNS (
      'DimDate'[DateKey],
      'DimDate'[Period],
      'DimAttribute1'[CategoryA],
      'DimAttribute2'[CategoryB],
      'DimAttribute3'[CategoryC],
      'DimEntityA'[EntityKey],
      'DimEntityB'[ExternalKey],
      'DimLocationA'[LocationCode],
      'DimLocationA'[DirectionCode],
      'DimGroupA'[GroupLabel],
      'DimEntityA'[EntityName],
      'DimAttribute1'[City],
      'DimAttribute1'[PostalCode],
      'DimStatus'[StatusLabel],
      'DimHierarchyA'[HierarchyLevel],
      'DimEntityA'[EntityType],
      'DimEntityA'[EntitySubtype],
      "MetricA_Main", [MetricA_Main],
      FILTER( DimTypeOrder, DimTypeOrder[OrderCode] IN { 11, 32} ),
      FILTER( DimDate, 'DimDate'[Period] = "2025-11" ),
      FILTER( DimAttribute4, 'DimAttribute4'[CategoryA]="All" )
      )

      • Mr_Robot0092's avatar
        Mr_Robot0092
        Icon for Helper I rankHelper I
        11 months ago

        amitchandak  sure i used all columns , but doesn't work