Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
csaba09
Frequent Visitor

Group and aggregate by calculated column

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_idcountry_idsales
11112
2224
3117
4115
5229

 

dim_product:

product_idproduct_type
1fruit
2fruit
3legume
4vegetable
5legume

 

dim_country:

country_idcountry_name
11Spain
22Portugal

 

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

country_namefruit_or_notsales
Spainfruit12
Spainnot_fruit12
Portugalfruit4
Portugalnot_fruit9

 

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!

4 ACCEPTED SOLUTIONS
tackytechtom
Super User
Super User

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:

 

tomfox_0-1663265663601.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

tamerj1
Super User
Super User

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]
                )
    )

View solution in original post

tackytechtom
Super User
Super User

Hi @csaba09 ,

 

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

tomfox_0-1663267558762.png

 

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/

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

tamerj1
Super User
Super User

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] )
)

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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] )
)

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!

tackytechtom
Super User
Super User

Hi @csaba09 ,

 

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

tomfox_0-1663267558762.png

 

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/

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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 🙂

tamerj1
Super User
Super User

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]
                )
    )

Thank you @tamerj1 , works like a charm!

tackytechtom
Super User
Super User

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:

 

tomfox_0-1663265663601.png

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors