Dynamic Ordering of Calculation Group Items (different order based on visual)

Hi Mikee_13 

To allow for different orderings of calculation items, you can add additional copies of the calculation item column as well as additional Ordinal columns to the calculation group, along with a few setup steps described below.

This is an extension of the method covered in this blog post.

Hopefully this is the sort of thing you are looking for 🙂

I have attached a simple example PBIX with a model set up in this way with a Time Intelligence calculation group.

The steps I used to set this up:

1. Created the original calculation group called Time Intelligence with calculation item column Time Calc.
2. Added a calculated column Time Calc Alternate which is just a copy of the original calculation item column:
   
   Time Calc Alternate ='Time Intelligence'[Time Calc]
3. Added a calculated column Ordinal Alternate containing the alternate ordering of calculation items. I used a SWITCH expression to set the value based on Time Calc value.
4. Set Time Calc Alternate to sort by Ordinal Alternate.
5. Critical step: Set Time Calc Alternate's Group By Columns property to Time Calc.
   See this article for detail on setting this property using Tabular Editor.

After following these steps, you can place either Time Calc or Time Calc Alternate in a visual and they will function correctly as calculation items and sort as specified by the Ordinal or Ordinal Alternate columns.

Field parameter to select between calculation group columns

• You can also create a Field Parameter to allow selection between the two orderings by the report consumer, by selecting between Time Calc or Time Calc Alternate.
• In the sample PBIX, I added a slicer to the page for this Field Parameter, but this could be applied via visual-level filter or could be tied to some dynamic logic (see this blog post).

I wouldn't call this a simple solution by any stretch, especially as the Group By Columns property is not accessible in the Power BI Desktop interface! But at least it works and is relatively straightforward to maintain.

Hopefully this is useful, and please post back with any further questions 🙂

Hi again Mikee_13 ,

Glad the solution for controlling the calculation item sort order worked! 🙂

On the question of filtering calculation items based on the "View" selection, your current measure-based filter might be suffering from too many computations on-the-fly.

Here is one method I tried that seems to perform acceptably with some test data:

PBIX: Calculation item order v2.pbix

1. Create a table View Time Calc that contains the required combinations of View and Calculation Item which is then related many-to-one to the field parameter table (View in my example).

To help create this, I first ensured that the Ordinal columns in the 'Time Intelligence' calculation group are blank for items that should be hidden:

'Time Intelligence' table:

View Time Calc table:

View Time Calc = 
GENERATE (
    SELECTCOLUMNS ( 'View', "View", 'View'[View] ),
    VAR CurrentView = [View]
    RETURN
        CALCULATETABLE (
            VALUES ( 'Time Intelligence'[Time Calc] ),
            SWITCH (
                CurrentView,
                "Summary", NOT ISBLANK ( 'Time Intelligence'[Ordinal Summary] ),
                "Act vs Bud", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsBud] ),
                "Act vs Frcst", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsFrcst] ),
                "Act vs PY", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsPY] )
            )
        )
)

Relationships

2. Create a second calculation group called Filter Time Intelligence that blanks out measures when the calc item is not in the set of calc items for that View. It has a single calculation item "Filter Time Calc by View":

IF (
    SELECTEDVALUE ( 'Time Intelligence'[Time Calc] ) IN VALUES ( 'View Time Calc'[Time Calc] ),
    SELECTEDMEASURE ( )
)

3. Lastly apply 'Filter Time Intelligence'[Filter] = "Filter Time Calc by View" as a filter on the required visuals:

Alternatives:

• You could try a blend of your original method using a measure-baesd filter and this method with a table of allowed calculation items.
• Another method I didn't mention (since I don't really encourage it) is simply to create additional copies of each calculation item with a grouping column (View in this case) added to the calculation group. This can be a maintenance nightmare since multiple copies of each calculation item need to be maintained.

I'd be interested in whether the above method improves performance with your model. 🙂