Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Is it possible to add additional columns to a Calculation group to provide alternative names or categories etc similar to how one can do for Named parameters?
Use Cases:-
1. Let's say I have 3 calculation items called MTD, QTD, YTD but I want to show it as "Month-To-Date", "Quarter-To-Date", "Year-To-Date" inn some places and perhaps as MTD, QTD, YTD in some places.
2. Let's say I have 10-15 calculationn items and I want to group them in to some logical groups to make selection easier.
Is there a way I can do that?
Solved! Go to Solution.
Hi @VickyDev18
(Disclosure: The blog posts linked below are my own.)
Yes 🙂 This post covers these types of situations:
https://owenaugerbi.com/creating-alias-columns-for-calculation-groups/
The main constraint is that columns can only be added to a calculation group table as DAX calculated columns. Also, calculation group tables cannot have relationships with other tables.
1. For alternative names, using your example, you could create a calculated columns such as this (assuming the original calculation item column is 'Time Intelligence'[Time Calc]:
Time Calc Alternative =
SWITCH (
'Time Intelligence'[Time Calc],
"MTD", "Month-To-Date",
"QTD", "Quarter-To-Date",
"YTD", "Year-To-Date",
'Time Intelligence'[Time Calc] -- use original name as default
)
For this to work correctly, you must then set the Group By Columns property of the new column to be the original calculation item column using Tabular Editor (see the post linked above), as well as setting the Sort By Column if desired.
There is an alternative method covered in this blog post as well.
2. For grouping or filtering columns, you also have to add calculated columns using appropriate DAX expressions.
You could use expressions that reference another table if you like (such as a hidden lookup table), but the calculation group table cannot have relationships with other tables.
A simple example of a grouping/filtering column might be:
Time Calc Group =
IF (
CONTAINSSTRING ( 'Time Intelligence'[Time Calc], "%" ),
"Percentage",
"Amount"
)
Hopefully this helps. Please post back if needed 🙂
Regards
Hi @VickyDev18
(Disclosure: The blog posts linked below are my own.)
Yes 🙂 This post covers these types of situations:
https://owenaugerbi.com/creating-alias-columns-for-calculation-groups/
The main constraint is that columns can only be added to a calculation group table as DAX calculated columns. Also, calculation group tables cannot have relationships with other tables.
1. For alternative names, using your example, you could create a calculated columns such as this (assuming the original calculation item column is 'Time Intelligence'[Time Calc]:
Time Calc Alternative =
SWITCH (
'Time Intelligence'[Time Calc],
"MTD", "Month-To-Date",
"QTD", "Quarter-To-Date",
"YTD", "Year-To-Date",
'Time Intelligence'[Time Calc] -- use original name as default
)
For this to work correctly, you must then set the Group By Columns property of the new column to be the original calculation item column using Tabular Editor (see the post linked above), as well as setting the Sort By Column if desired.
There is an alternative method covered in this blog post as well.
2. For grouping or filtering columns, you also have to add calculated columns using appropriate DAX expressions.
You could use expressions that reference another table if you like (such as a hidden lookup table), but the calculation group table cannot have relationships with other tables.
A simple example of a grouping/filtering column might be:
Time Calc Group =
IF (
CONTAINSSTRING ( 'Time Intelligence'[Time Calc], "%" ),
"Percentage",
"Amount"
)
Hopefully this helps. Please post back if needed 🙂
Regards
Thanks @OwenAuger for the detailed response. I think i'll skip the aliasing part given the additional settings required to make it work but grouping seems easy enough through either calculated column approach or lookup.
Thanks for all the details. Learnt quite a few new things.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |