Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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]
)
)
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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] )
)
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!
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on 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 🙂
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]
)
)
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |