topic Re: Group and aggregate by calculated column in DAX Commands and Tips

Group and aggregate by calculated column

csaba09 — Thu, 15 Sep 2022 17:50:18 GMT

Hi All,

I'd like to write a DAX query that returns a table containing an aggregation grouped by a physical column AND a calculated column.

I have the below tables in the model:

fact_sales:

product_id	country_id	sales
1	11	12
2	22	4
3	11	7
4	11	5
5	22	9

dim_product:

product_id	product_type
1	fruit
2	fruit
3	legume
4	vegetable
5	legume

dim_country:

country_id	country_name
11	Spain
22	Portugal

I'd like to sum sales and group by country name and whether the product is 'fruit' or 'not fruit', returning the below:

country_name	fruit_or_not	sales
Spain	fruit	12
Spain	not_fruit	12
Portugal	fruit	4
Portugal	not_fruit	9

So the key here is I want to create a calculated 'fruit_or_not' column in the query and use that for grouping later in the same query.

How would I go about this?

I tried creating a table variable and then using that in a SUMMARIZECOLUMN:

EVALUATE VAR enriched_dim_product = ADDCOLUMNS( dim_product, "fruit_or_not", IF(dim_product[product_type] = "fruit", "fruit", "non_fruit") ) RETURN SUMMARIZECOLUMNS( dim_country[country_name], enriched_dim_product[fruit_or_not], "sales", SUM(fact_sales[sales]) )

But this throws an error saying it cannot find the table called 'enriched_product_table'. I know I could access 'enriched_product_table' and the 'fruit_or_not' column using SUMMARIZE, but then I can't figure out how to also bake in the country name and the sum of sales.

Any tips?

Thanks!

Re: Group and aggregate by calculated column

tackytechtom — Thu, 15 Sep 2022 18:16:29 GMT

Hi csaba09 ,

In your case (which I pressume is a simplification of the real problem), I would have just added the "fruit_or_not" attribute on to the product dimension (calculated column or in Power Query). Then Power BI woild do the grouping for you due to filter transition:

Would that one also work in your case? I mean you would need the fruit_or_not attribute anyway if you'd like to show it in a graph?

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Group and aggregate by calculated column

tamerj1 — Fri, 16 Sep 2022 03:39:59 GMT

Hi csaba09

please try

EVALUATE VAR T1 = SUMMARIZE ( fact_sales, dim_country[country_name], dim_product[product_type], "@sales", SUM ( fact_sales[sales] ) ) VAR T2 = ADDCOLUMNS ( T1, "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" ) ) RETURN SUMMARIZE ( T2, [country_name], [fruit_or_not], "sales", VAR Country = [country_name] VAR Fruit = [fruit_or_not] RETURN SUMX ( FILTER ( T2, [country_name] = Country && [fruit_or_not] = Fruit ), [@sales] ) )

Re: Group and aggregate by calculated column

tackytechtom — Thu, 15 Sep 2022 18:46:32 GMT

Hi csaba09 ,

I only now noticed that you were explicitly asking for a table variable. Here my shot:

tableNew = VAR _table1 = SUMMARIZE ( fact_sales, dim_country[country_name], dim_product[product_type], fact_sales[sales] ) VAR _table2 = ADDCOLUMNS ( _table1, "fruit_or_not", IF ( [product_type] = "fruit", "fruit", "non_fruit" ) ) RETURN GROUPBY ( _table2, [country_name], [fruit_or_not], "sales", SUMX(CURRENTGROUP(), [sales]) )

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Group and aggregate by calculated column

csaba09 — Thu, 15 Sep 2022 21:30:46 GMT

Thank you tamerj1 , works like a charm!

Re: Group and aggregate by calculated column

csaba09 — Thu, 15 Sep 2022 21:38:57 GMT

Thanks tackytechtom , the query works well! And as you mentioned in your other reply of course it's probably best to include the extra column in the model, spot on! Only this time I wanted to see if we can do the magic already when DAX querying the SSAS cube because with the real data I'm working with this will make a BIG difference in the row number that arrives to the Query Editor from the cube 🙂

Re: Group and aggregate by calculated column

tamerj1 — Fri, 16 Sep 2022 03:38:33 GMT

Hi csaba09
Actually there is a much simpler solution by defining a new column

DEFINE COLUMN dim_product[fruit_or_not] = IF ( dim_product[product_type] = "fruit", "fruit", "non_fruit" ) EVALUATE SUMMARIZECOLUMNS ( dim_country[country_name], dim_productt[fruit_or_not], "sales", SUM ( fact_sales[sales] ) )

Re: Group and aggregate by calculated column

csaba09 — Fri, 16 Sep 2022 07:13:35 GMT

Now this feels a lot cleaner tamerj1 code-wise and also because we only summarize/group once instead of twice. Many thanks, this is super!