cancel
Showing results for 
Search instead for 
Did you mean: 

Dax function that behave like Power Query Group by

Hello,

 

I'm trying this to learn more about Power Query Performance vs. DAX Performance. I'm trying to use the below DAX function to see if I can replicate the Power Query Group By process. But whenever I run the DAX code below, I get a "WebView2" error.

The Table I'm using in my DAX function has 28 columns with 645K + rows; with the below code, I'm looking to create a new table that has two columns, where I'm merging rows with the same ID and concatenating the groups with a comma to separate the values.

Note: When using the Power Query Group By, the new Table will have two columns with 260K + rows.

DaxGroupedByTable =
var tempTable =
DISTINCT(
SELECTCOLUMNS(
'Table',
"ID",
'Table'[CustomerID],
"Groups",
'Table'[GroupName]
)
)
return
SUMMARIZE(tempTable,[ID],"Groups_CSV",CONCATENATEX(tempTable,[Groups],","))

FYI: the GroupBy DAX function doesn't work with Concatenating text, so I ended up using the Summarize function.

Thanks in advance to anyone that spends time on this and provides their input on this issue.
Status: Investigating

Hi @IyadSlim,

 

May I know did you see any detailed error info in addition to “WebView2 error”?

 

How do you would like to group your table?

vcazhengmsft_0-1656929166038.png

 

vcazhengmsft_1-1656929166041.png

 

Best Regards,

Community Support Team _ Caiyun

Comments
v-cazheng-msft
Community Support
Status changed to: Investigating

Hi @IyadSlim,

 

May I know did you see any detailed error info in addition to “WebView2 error”?

 

How do you would like to group your table?

vcazhengmsft_0-1656929166038.png

 

vcazhengmsft_1-1656929166041.png

 

Best Regards,

Community Support Team _ Caiyun

IyadSlim
Regular Visitor

Hello @v-cazheng-msft ,

 

First of all, thanks for taking the time to look at this.

 

Second, yes, in your example, when you execute the dax code, this is the outcome I'm looking for, but when I run the dax code on my Dataset, I get the error message below:

IyadSlim_0-1657041960423.png

When I click on the "Get in touch" button, this is the link that opens for me:
Power BI Desktop WebView2 Out Of Memory - Microsoft Power BI Community
Once the link is opened, Power BI Desktop will close independently.

So I'm assuming that the operation I'm trying to perform using the dax code above is too complex to be done in DAX and eventually runs out of memory.

FYI: my machine that runs desktop has 32 GB of ram, and when I attempt to execute the code, I make sure that I have at least 16GB  available.

Note: the Dataset has 640K rows.

 

Regards,

Iyad Slim