Forum Discussion

Iaine's avatar
Iaine
Frequent Visitor
8 months ago
Solved

How to Sort Pie Chart Values When Using SSAS Measures?

I’m trying to configure a pie chart so that a specific value appears first. After searching online, it seems the usual approach is to use a custom table or add a column for sorting.

Here’s the challenge: my data comes from an SSAS model, and I don’t have permission to modify it. The values are measures that together add up to 100%, which is why they work well in a pie chart. However, since they are three separate measures, I can’t sort them directly.

Has anyone dealt with this before? Any ideas on how to proceed without changing the SSAS model?

Pie
SSAS
  • GeraldGEmerick's avatar
    GeraldGEmerick
    8 months ago

    Iaine What does "first" mean in a pie chart? Like first on the Legend? That is simply a matter of your order of your measures in the Values field of the pie chart. 

5 Replies

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    8 months ago

    Iaine What does "first" mean in a pie chart? Like first on the Legend? That is simply a matter of your order of your measures in the Values field of the pie chart. 

    • Iaine's avatar
      Iaine
      Frequent Visitor
      8 months ago

      Oh my god i was looking for some wierd over complicated solution. It was that simple awesome thanks loads

       

  • vivien57's avatar
    vivien57
    Super User
    8 months ago

    Hello,

    Without access to the SSAS model, it's difficult to do anything properly.

    Can you create a local table in your Power BI ? If Yes, try this :

    1. Enable (if possible) the DirectQuery for Power BI datasets and AS feature and switch your report to a composite model (in Desktop: File → Options → Preview features if needed, then connect in DirectQuery to the compatible dataset/AS).

    2. Create a disconnected table (in Power BI Desktop):
    MeasuresPie =
DATATABLE(
    "Label", STRING,
    "Order", INTEGER,
    {
        {"My Measure A", 1},
        {"My Measure B", 2},
        {"My Measure C", 3}
    }
)
​

     

    • Create a SWITCH measure that returns the SSAS measure based on the selected label:
    Pie Value :=
VAR lbl = SELECTEDVALUE(MeasuresPie[Label])
RETURN
    SWITCH(
        TRUE(),
        lbl = "My Measure A", [MeasureA_SSAS],
        lbl = "My Measure B", [MeasureB_SSAS],
        lbl = "My Measure C", [MeasureC_SSAS],
        BLANK()
    )
​

     

    Have a nice day,

    Vivien

  • Zanqueta's avatar
    Zanqueta
    Super User
    8 months ago

    Hi Iaine,

     

    This is a common limitation when working with SSAS models because you cannot add custom columns or tables for sorting. However, there are a couple of approaches you can use directly in Power BI without modifying the SSAS model:

    1. Create a Composite Table in Power BI Using DAX

    Since your measures are separate and you want them sorted, you can create a small calculated table in Power BI that references these measures and includes a sort order.
    Example:
     
    SortTable =
DATATABLE(
    "Label", STRING,
    "SortOrder", INTEGER,
    {
        {"Measure A", 1},
        {"Measure B", 2},
        {"Measure C", 3}
    }
)
    Then create a measure for each value:
     
    MeasureValue =
SWITCH(
    SELECTEDVALUE(SortTable[Label]),
    "Measure A", [MeasureA],
    "Measure B", [MeasureB],
    "Measure C", [MeasureC]
)
    Use SortTable[SortOrder] to sort the pie chart. This way, you control the order without touching the SSAS model.
     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.