Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Summarize function error: the expression refers to multiple columns

Hi experts, When using the expression below I am getting the error:  Summarize function error: the expression refers to multiple columns Any idea what I am missing?   MyMeasure:=SUMMARIZE( Range...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    Irwan has explained very clearly why this error is reported and given a solution. If you want to get the summarized values group by the data, you can also create a table visual in Power BI directly with below setttings to get it:

    Hi Canguru ,

    You can refer the following link to insert a pivot table to get it just as shown in below screenshot:

    Power Pivot Principles: Introducing the SUMMARIZE Function

    Best Regards

Irwan's avatar
Irwan
Super User
1 year ago

hello Anonymous 

 

your DAX works perfectly if your expected result is a table.

 

however, looks like you put the DAX into measure which has scalar return value (not a column) the you got this error.

here is return value of SUMMARIZE() based on SUMMARIZE function (DAX) - DAX | Microsoft Learn

 

you can use SUMMARIZE() in measure, but i believe you need to do SUMMARIZE() in virtual table then take the value from virtual table as your measure result.

 

Hope this will help.

Thank you.

  • Canguru's avatar
    Canguru
    Regular Visitor
    1 year ago

    Thanks Irwan ,

    How did you return a virtual table using the summarize expression? I have created the measure using PowerPivot. How can I create the virtual table in PowerPivot?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous ,

      Irwan has explained very clearly why this error is reported and given a solution. If you want to get the summarized values group by the data, you can also create a table visual in Power BI directly with below setttings to get it:

      Hi Canguru ,

      You can refer the following link to insert a pivot table to get it just as shown in below screenshot:

      Power Pivot Principles: Introducing the SUMMARIZE Function

      Best Regards

    • Irwan's avatar
      Irwan
      Super User
      1 year ago

      hello Canguru 

       

      i would do something like this.

      MyMeasure = 
      var _Sum = 
      SUMMARIZE(
          'Range',
          'Range'[Date],
          'Range'[Sales]
      )
      Return
      SUMX(
          _Sum,
          'Range'[Sales]
      )
      Virtual table stored as variable then calculate any value from that variable.
       
      Hope this will help.
      Thank you.