Forum Discussion

niemis's avatar
niemis
Helper I
1 year ago
Solved

Need help with DAX Measures and Ranking

On my canvas, I have 4 visuals.  Each visual has filters associated with only them (opened, closed, etc.)  I have 2 slicers, one for organization and one for service grp.  When I select a slicer, all...
Need Help
  • niemis's avatar
    niemis
    1 year ago

    v-sgandrathi Hello.  I tried to do this, but gave up as I had multiple visuals that are synced.  So I am skipping the top 3 and bottom 3, and just showing the sort by highest SLA.  If I get to the point where management is asking for only the top 3, then I'll revisit this.  And as for the bottom 3, if there was nothing that met the .8 or lower filter, there was nothing to show, which led to confusion.  Thank you for your help.  I really appreciate it.  

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

Hi niemis,

Thanks for reaching out to the Microsoft fabric community forum. 

 

Create 2 Measures : 

 

Measure 1: Highest SLA of Top Ticket Count :

 

TopTicket_HighSLA = 
VAR TopTicketGroup = 
    CALCULATETABLE(
        TOPN(
            1,
            VALUES('Table'[Assignment Group]),
            CALCULATE(SUM('Table'[Total])),
            DESC
        ),
        ALLSELECTED('Table')
    )
RETURN
CALCULATE(
    MAX('Table'[SLA Achievement]),
    FILTER(
        'Table',
        'Table'[Assignment Group] IN TopTicketGroup
    )
)

Measure 2: Lowest SLA of Top Ticket Count :

 

TopTicket_LowSLA = 
VAR TopTicketGroup = 
    CALCULATETABLE(
        TOPN(
            1,
            VALUES('Table'[Assignment Group]),
            CALCULATE(SUM('Table'[Total])),
            DESC
        ),
        ALLSELECTED('Table')
    )
RETURN
CALCULATE(
    MIN('Table'[SLA Achievement]),
    FILTER(
        'Table',
        'Table'[Assignment Group] IN TopTicketGroup
    )
)

 

Add Visuals :

 

Table -

Assignment Group

Total

SLA Achievement

 

Slicers -

Department, Month

 

Two card visuals (or KPI visuals):

Card 1: TopTicket_HighSLA

Card 2: TopTicket_LowSLA

 

Now the result will:

Filter dynamically based on department/month

Show top ticket group's best and worst SLA%

 

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

 

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

 

Best Regards,
Sahasra.
Community Support Team 

niemis's avatar
niemis
Helper I
1 year ago

v-sgandrathi Thank you.  But this is showing the same value for both top ticket high sla and top bottom ticket low sla.  What I need to see are the top 3 totals with their sla.  Then the bottom 3 will be tricky because essentially I don't want to see totals that are like 0, because there are many.  

 

 

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi niemis,

     

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    Create Rank Measure

     

    Rank_TicketCount =
    RANKX(
        FILTER(
            ALLSELECTED('TicketData'[Assignment Group]),
            CALCULATE(SUM('TicketData'[Total])) > 0
        ),
        CALCULATE(SUM('TicketData'[Total])),
        ,
        DESC
    )

     

    This ranks assignment groups by ticket count (Total), ignoring groups with 0 tickets.

    Create a Filter Measure for Top 3

     

    IsTop3_Tickets = 
    IF([Rank_TicketCount] <= 3, 1, 0)

     

    This creates a flag (1/0) to identify the top 3 groups.

    Table Visual -

    Assignment Group

    Total

    SLA Achievement

     

    Now your table will only show the top 3 groups by ticket count and their SLA %.

     

    Filter Pane  -

    Drag - IsTop3_Tickets to Visual-level filters.

     

    Now your table will only show the top 3 groups by ticket count and their SLA %.

     

    Slicers -

    Add the Department field to the slicer.

    Add another slicer for Month.

    Now when you select a department or month, your top 3 table will update dynamically based on filter context.

     

    1.Create this measure:

     

    Rank_TicketCount_Asc =
    RANKX(
        FILTER(
            ALLSELECTED('TicketData'[Assignment Group]),
            CALCULATE(SUM('TicketData'[Total])) > 0
        ),
        CALCULATE(SUM('TicketData'[Total])),
        ,
        ASC
    )

    2.Then:

     

    IsBottom3_Tickets = 
    IF([Rank_TicketCount_Asc] <= 3, 1, 0)

    3.Use it in a new table visual -

     

    and filter on IsBottom3_Tickets=1.

     

    Find attached .Pbix file for your reference -

     

     

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

     

    Best Regards,
    Sahasra.
    Community Support Team 

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      Hi niemis,

       

      I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

      If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

       

      Thank you.

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        Hi niemis,

          

        We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

        If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

        If you need any further assistance, feel free to reach out.

        Thank you for being a valued member of the Microsoft Fabric Community Forum!