Forum Discussion
Sorting by Calculation item with multiple measures
Hi. I have created a calcuation group to do time intelligence so i have Current Qtr and Prev Qtr which allows me ot Calcualte Qtr on Qtr change (QoQ)
I have used these as column headers and have 4 regular measures as below
My client wants ot use this table to see which if his cusotmers have shown the greatest change QoQ in the 4th measure (Case Instructed...). The report seems ot be automatically sorting by the Current Qtr of this 4th measure. I have no idea why it doesnt sort by Case Instructed or one of the other 3 fields? I think possible it has kept the sort order when i didnt have an calcuation groups and only this measure. More critically is there anyway to force the sort to be by the QoQ Case Instructed column i.e. 8th column in the table?
Absolutely stumped how to make this useful
thnaks any advice
Mike
Hi again masplin 🙂
From your screenshot/description, it looks as though you've done everything correctly!
I can't immediately tell what's causing the errors 😕
Double-checking that the updated calculation group has been saved back to the model and processed/refreshed in Power BI Desktop (assuming you're working in Desktop)?
On your specifc questions:
- Yes, you are correct in replacing Time Intelligence[Time calc] with TimeIntelligence[Calculation Items].
- Yes, in the Sorting calculation group you need one calculation per sorting option. And the format string expression SELECTEDMEASUREFORMATSTRING() is correct since we want to retain the existing format string.
Below is a screenshot from TE2 connected to my model with some annotations in case that helps. I guess you could also open both models in TE2 and compare side-by-side.
Also, if you're able to share a cut-down or sanitised PBIX (or BIM file exported from TE) I could take a further look at my end (realise sharing may not be an option though).
Regards
Hi again masplin,
Just managed to return to look at this now 🙂
To make the TopN filter work correctly, we have to prevent the measure used in TopN filter being impacted by the Sorting calculation group.
One method is to create a special measure identical to the original measure with a special name, so that we can identify it and ensure that the Sorting calculation group doesn't apply.
In the attached PBIX I have:
- Created this measure to be used in the TopN filter. The "TopN" in the name is used to identify it.
Cases Instructed (£) (1M Lag) per 30D QoQ TopN = [Cases Instructed (£) (1M Lag) per 30D QoQ] - Used this new measure in the TopN filter on the matrix visual.
- Modifed the Sorting calculation item definitions as shown below.
-- Calculation Item "Sort by Current Qtr" VAR TopNMeasureSelected = CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "TopN" ) RETURN IF ( NOT ( ISINSCOPE ( TimeIntelligence[Formula] ) ) && NOT TopNMeasureSelected, CALCULATE ( SELECTEDMEASURE ( ), TimeIntelligence[Formula] = "Current Qtr" ), SELECTEDMEASURE ( ) )-- Calculation Item "Sort by QoQ" VAR TopNMeasureSelected = CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "TopN" ) RETURN IF ( NOT ( ISINSCOPE ( TimeIntelligence[Formula] ) ) && NOT TopNMeasureSelected, CALCULATE ( SELECTEDMEASURE ( ), TimeIntelligence[Formula] = "QoQ" ), SELECTEDMEASURE ( ) )There are other ways of achieving this but this seems a convenient method.
Regards
- Created this measure to be used in the TopN filter. The "TopN" in the name is used to identify it.
8 Replies
Hi masplin
Here is a method I've used in the past.
- The main idea is that the matrix will be sorted by whichever measure is selected to sort by, but at a "total" level. In this case, "total" refers to the filter context where no calculation item is filtered (even though that total is not visible in the visual).
- So we can force sorting by a particular measure and calculation item by
- Setting the visual to sort by the underlying measure in the usual way (More Options > Sort menu).
- Creating a second calculation group with a calculation item that detects whether or not the first calculation group is filtered as a grouping column in the current context. If it is not (which is equivalent to the "total" level of the visual at the calc group level), it modifies the selected measure to apply the desired "sort-by" calculation item from the original calculation group.
- Filtering the second calculation group to select the desired sort-by calculation item.
I have attached a sample PBIX of my own to illustrate:
- In the screenshot, I have selected Sales as the measure to sort by.
- My second calculation group is called Sorting, and contains options for each calculation item that you want to allow sorting by. The selected calculation item in the example is "Sort by Current".
- The Tabular Editor DAX Scipt for this calculation group is below:
So the steps to change the sorting rule in the report are:
- Select the sorting measure by selecting it on the visual in the usual way (More Options > Sort menu)
- Select the appropriate option from a slicer or filter on the Sorting calculation group.
------------------------------- -- Calculation Group: 'Sorting' ------------------------------- CALCULATIONGROUP Sorting[Sort Option] Precedence = 3 CALCULATIONITEM "Sort by Current" = IF ( NOT ( ISINSCOPE ( 'Time Intelligence'[Time Calc] ) ), CALCULATE ( SELECTEDMEASURE ( ), 'Time Intelligence'[Time Calc] = "Current" ), SELECTEDMEASURE ( ) ) FormatString = SELECTEDMEASUREFORMATSTRING ( ) CALCULATIONITEM "Sort by Year on Year" = IF ( NOT ( ISINSCOPE ( 'Time Intelligence'[Time Calc] ) ), CALCULATE ( SELECTEDMEASURE ( ), 'Time Intelligence'[Time Calc] = "Year on Year" ), SELECTEDMEASURE ( ) ) FormatString = SELECTEDMEASUREFORMATSTRING ( )Hopefully that helps, and can be adapted to your model.
Regards
Hi Owen
Seems very elegant, but something slightly wrong
firstly have I done the right thing replacing your Time Intelligence[Time calc] with TimeIntelligence[Calculation Items] as that what my tree looks like?
Secondly I created 2 calcuation items one for each sort is that correct? I put SELECTEDMEASUREFORMATSTRING ( ) in the Format String Expression
I'm getting an error in the expression Calculation Items in table TimeIntelligence cannot be found
Lastly its throwing an error "The Syntax for FormatString is incorrect". I'm pretty sure I copied your code exactly , but there are no clues as to what it doesnt like.
I'm basically not sure how to implement your code in the Tabular editor.
Thanks
Mike
Hi again masplin 🙂
From your screenshot/description, it looks as though you've done everything correctly!
I can't immediately tell what's causing the errors 😕
Double-checking that the updated calculation group has been saved back to the model and processed/refreshed in Power BI Desktop (assuming you're working in Desktop)?
On your specifc questions:
- Yes, you are correct in replacing Time Intelligence[Time calc] with TimeIntelligence[Calculation Items].
- Yes, in the Sorting calculation group you need one calculation per sorting option. And the format string expression SELECTEDMEASUREFORMATSTRING() is correct since we want to retain the existing format string.
Below is a screenshot from TE2 connected to my model with some annotations in case that helps. I guess you could also open both models in TE2 and compare side-by-side.
Also, if you're able to share a cut-down or sanitised PBIX (or BIM file exported from TE) I could take a further look at my end (realise sharing may not be an option though).
Regards
Hi again masplin,
Just managed to return to look at this now 🙂
To make the TopN filter work correctly, we have to prevent the measure used in TopN filter being impacted by the Sorting calculation group.
One method is to create a special measure identical to the original measure with a special name, so that we can identify it and ensure that the Sorting calculation group doesn't apply.
In the attached PBIX I have:
- Created this measure to be used in the TopN filter. The "TopN" in the name is used to identify it.
Cases Instructed (£) (1M Lag) per 30D QoQ TopN = [Cases Instructed (£) (1M Lag) per 30D QoQ] - Used this new measure in the TopN filter on the matrix visual.
- Modifed the Sorting calculation item definitions as shown below.
-- Calculation Item "Sort by Current Qtr" VAR TopNMeasureSelected = CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "TopN" ) RETURN IF ( NOT ( ISINSCOPE ( TimeIntelligence[Formula] ) ) && NOT TopNMeasureSelected, CALCULATE ( SELECTEDMEASURE ( ), TimeIntelligence[Formula] = "Current Qtr" ), SELECTEDMEASURE ( ) )-- Calculation Item "Sort by QoQ" VAR TopNMeasureSelected = CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "TopN" ) RETURN IF ( NOT ( ISINSCOPE ( TimeIntelligence[Formula] ) ) && NOT TopNMeasureSelected, CALCULATE ( SELECTEDMEASURE ( ), TimeIntelligence[Formula] = "QoQ" ), SELECTEDMEASURE ( ) )There are other ways of achieving this but this seems a convenient method.
Regards
- Created this measure to be used in the TopN filter. The "TopN" in the name is used to identify it.
