cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Trying to change a Calculated Column category to a Measure

Apologies in advance if this question is too simple, however I'm struggling to find an answer to this myself as a power bi newbie. I am creating many calculated columns and I am struggling to convert them into measures.

For example, in a sales table, I want to add a sales category:

Sales Category Calculated Column = SWITCH(True, Sales[Total] <= 100, "Low Value", Sales[Total] <= 1000, "Mid Value", Sales[Total] > 1000, "High Value")

This will essentially add on a calculated column, giving each sale a low/med/high category. I can then generate charts, using the count of key as the value, and the sales category as the legend, and show splits of low, med and high sales. I find this easy and intuitive - but its obviously adding a lot of data and columns to the model.

How would I do the equivalent of something like this using Measures?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

According to your description, you need to add aggregate functions like sum, max, and min to convert column to measure. I did the test reference as follows:

M_Result =
VAR a =
CALCULATE ( SUM ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
IF (
a <= 100,
"Low Value",
IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
)
M_1 =
VAR a =
MAX ( 'Table'[Total] )
RETURN
IF (
a <= 100,
"Low Value",
IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
)

For more details, you can read related blog:

Calculated Columns and Measures in DAX - SQLBI

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @Anonymous ,

According to your description, you need to add aggregate functions like sum, max, and min to convert column to measure. I did the test reference as follows:

M_Result =
VAR a =
CALCULATE ( SUM ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
IF (
a <= 100,
"Low Value",
IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
)
M_1 =
VAR a =
MAX ( 'Table'[Total] )
RETURN
IF (
a <= 100,
"Low Value",
IF ( a > 100 && a <= 1000, "Mid Value", "High Value" )
)

For more details, you can read related blog:

Calculated Columns and Measures in DAX - SQLBI

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Anonymous The equivalent measure would be:

Sales Category Calculated Column =
SWITCH(TRUE(),
SUM(Sales[Total]) <= 100, "Low Value",
SUM(Sales[Total]) <= 1000, "Mid Value",
SUM(Sales[Total]) > 1000, "High Value"
)

Column references in measures must have an aggregation like MAX, SUM, etc. Now, measures have restrictions on their use in axis, legends, etc. You may find the Disconnected Table Trick handy in those instances as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors