cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianml
Post Patron
Post Patron

Sort by Measure column

Hi,

i need to sort the pivot table by a column that is a Measure but it doesn't allow me to do it:

 

cristianml_0-1593439915994.png

How can I sort by this column (Risk Level) ?

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

You need to sort by column in the Power Pivot section. Measures are not columns, they just really float in the model. While they are stored in tables, they are not really part of the table and can be moved to other tables with no conseqeuence. It is why when you refer to a measure in another measure, you don't use the table name. Measures do not have a list of values in them. The measure is calculated each time it is encountered in the visual or pivot table based on the filter context. So there is nothing for the DAX engine to sort by.

 

You need to either:

  • create a custom column in Power Query, then use that column to sort on. This is the recommended way as Calculated Columns (next option) are best avoided when possible.
  • create a Calculated Column in DAX/Power Pivot.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User III
Super User III

You need to sort by column in the Power Pivot section. Measures are not columns, they just really float in the model. While they are stored in tables, they are not really part of the table and can be moved to other tables with no conseqeuence. It is why when you refer to a measure in another measure, you don't use the table name. Measures do not have a list of values in them. The measure is calculated each time it is encountered in the visual or pivot table based on the filter context. So there is nothing for the DAX engine to sort by.

 

You need to either:

  • create a custom column in Power Query, then use that column to sort on. This is the recommended way as Calculated Columns (next option) are best avoided when possible.
  • create a Calculated Column in DAX/Power Pivot.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors