topic Re: Grouping multiple rows in 1 based on one column value in Power BI in DAX Commands and Tips

Grouping multiple rows in 1 based on one column value in Power BI

sachingupta31 — Wed, 22 Nov 2023 18:05:17 GMT

Hello,

I have below data in power BI.

URL	Count
/XXX/v1/sac	5
/XXX/v1/sac/	10

What I would like to do is group the URL column value after cleaning them and then the final output should be like below

URL	Count
/XXX/v1/sac	15

For first step I had removed the '/' from the end to make the URL same so that we can group. Below is the expression I have written.

Right (col1], 1) = "/",
LEFT(col1, LEN(col1) -1),
col1
)

which gives the output = /XXX/v1/sac

For 2nd step I am trying to group using SUMMARIZE DAX function, but it is not working for me. Can someone help on to achieve the desired output?

Thanks,

Sachin

Re: Grouping multiple rows in 1 based on one column value in Power BI

Rupak_bi — Wed, 22 Nov 2023 19:20:10 GMT

if the Url exactly matches, it should work. please share sample data to check.

Re: Grouping multiple rows in 1 based on one column value in Power BI

sachingupta31 — Thu, 23 Nov 2023 00:12:43 GMT

Hi Rupak_bi PFA, sample data and output. Also, col1 is a column in a table and Col2 is measure created using below formula.
col2 Measure formula= CALCULATE(COUNT(Related Features))

please let me know if it works.

Re: Grouping multiple rows in 1 based on one column value in Power BI

Anonymous — Thu, 23 Nov 2023 05:00:42 GMT

Hi sachingupta31 ，

Below is my table:

The following DAX might work for you:

Measure = CALCULATE( SUM('Table'[col2]), ALLEXCEPT('Table','Table'[col 1]) )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.