topic Re: How to Group By with several columns? in DAX Commands and Tips

How to Group By with several columns?

Anonymous — Fri, 24 Sep 2021 13:07:45 GMT

Hi,

I have a simple table with 3 columns as shown in the attached picture:

- I tried to use Group By (in Query Editor) and could get the results in the column F, but not G.

please help

Re: How to Group By with several columns?

colacan — Fri, 24 Sep 2021 15:16:28 GMT

For number of product, we should use COUNT function and for Number of Cancelled Product, you can use SUM function.

If expressed in DAX, the table can be made as below

=========================
ADDCOLUMNS(
    SUMMARIZE( OrderTable, OrderTable[OrderNumber] ),
    "TotNumberOfProduct", CALCULATE( COUNT( OrderTable[OrderNumber] ) ),
    "NumberOfCancellation", CALCULATE( SUM( OrderTable[Cancel] ) )
)

Re: How to Group By with several columns?

Anonymous — Sat, 25 Sep 2021 09:57:53 GMT

Thank you for a fast answer!

I tried this solution and got a new table with the result i wanted - Awesome!

So, if i want to transform/make my existing table to look like the new table then i have to do it in the query editor (with M language), isnt it?

Creating an additional table is abit messy right now , since i have too many tables and I am starting to loose track of them :).

Re: How to Group By with several columns?

Jos_Woolley — Sat, 25 Sep 2021 11:34:19 GMT

Hi,

Regards

Re: How to Group By with several columns?

colacan — Sat, 25 Sep 2021 20:11:56 GMT

Probably.. Because DAX makes new table using other data table(s). (in this case, the first table)

However, if the first table is also made from some other raw data, you can skip the first table and make the 2nd table using the raw data. Of cuase, you need different DAX code for it.

Thanks