Sorting by Calculation item with multiple measures

[masplin]

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

[OwenAuger · Accepted solution]

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:

1. Yes, you are correct in replacing Time Intelligence[Time calc] with TimeIntelligence[Calculation Items].
2. 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

[OwenAuger · Accepted solution]

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:

1. 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]
2. Used this new measure in the TopN filter on the matrix visual.
3. 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

[masplin]

Hi Owen

That's all very elegant. Thank you so much for looking at this. Seems to work perfectly now

Mike

[masplin]

Hi Owen. M now in China for a few weeks so wont get look at this for a bit, but appreciate your advice.

thanks

mike

[OwenAuger]

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
  1. Setting the visual to sort by the underlying measure in the usual way (More Options > Sort menu).
  2. 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.
  3. 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:

1. Select the sorting measure by selecting it on the visual in the usual way (More Options > Sort menu)
2. 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

[masplin]

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

[OwenAuger · Accepted solution]

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:

1. Yes, you are correct in replacing Time Intelligence[Time calc] with TimeIntelligence[Calculation Items].
2. 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

[masplin]

Ah found it. Your table list "Time Calc" is actually my "Formula" not "Calcuation items"

No errors now works perfectly. Genius thanks so much

[masplin]

Hi Owen

Slightly scratching my head though becuase using your sort methodolgy is changing the Top N filter which i dont understand how it is interacting

This version I have created a measure for QoQ so I can use it as Top N filter for Customers, so its duplicating the 3rd column which is the Tabualr Editor QoQ calc.  Its sorted on the first column.

However if I click sort by QoQ I get actually differnet cusomters in the Top10 filter?  McAlister Family Law is not in the first table and must be way down the ranking as QoQ is negative.  It seems to have change the Top10 to look at the last column which is the QoQ measure calcuated for the previous quarter so like 2 quarters ago.  Somehow the Sort measure is feeding in the previous QoQ to the Top 10 filter?

Model 

I've created a cut down version