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 visuals are reflected.  One particular table is for ticket count and ticket SLA%.  It has column headings Month (from Calendar table), which is filtered for previous month, organization, assignment group, ticket count, and sla attainment (from Incident table).  

 

I'm trying to create a way to show the 'top' 3 ticket count and the highest SLA %.  I tried using the Top N filter, but it didn't work as there are some assignment groups with 0 tickets.  This table will change when a particular organization is selected in the slicer. 

 

I've tried using ChatGPT to help me, but I haven't been able to get the display correct.  

 

Once I am able to get this to work, I will make an exact copy of the table and will have the need to show the highest number of ticket count and lowest SLA%.  Of course, again this will change depending on the slicer selection.  

 

Here is a what my table looks like: And it is sorted by Total.  I'm hoping someone has some advice.  Unfortunately, I can't share the pbix file.  

 

MonthDepartmentAssignment GroupTotalSLA Achievement
25-AprDevelopment BaT SW12373.17%
25-AprDevelopment BSE Compute10176.24%
25-AprDevelopment BSE Storage8068.75%
25-AprDevelopment Componentization2458.33%
25-AprDevelopment DevApps Nagios9694.79%
25-AprDevelopment Testing IA2572.00%

 

 

 

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.  

19 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi niemis can you pls share what your table looks like? sample pbix file if possible?

    • niemis's avatar
      niemis
      Helper I
      1 year ago

      Here is a table that I am working with.  Unfortunatley, I cannot share a pbix file for confidential and security reasons.  

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
    • niemis's avatar
      niemis
      Helper I
      1 year ago

      Here is a table that I am working with.  Unfortunatley, I cannot share a pbix file for confidential and security reasons.  

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
    • niemis's avatar
      niemis
      Helper I
      1 year ago

      ajaybabuinturi Thank you.  I read through this blog, but I don't know how it will work given the SLA% field and Incident count because they vary by group, and the numbers aren't static.  

    • niemis's avatar
      niemis
      Helper I
      1 year ago

      Here is a table that I am working with.  Unfortunatley, I cannot share a pbix file for confidential and security reasons.  

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
    • niemis's avatar
      niemis
      Helper I
      1 year ago

      danextian Thank you.  Here is what I'm trying to do:  

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi niemis,

     

    I wanted to check if you had the opportunity to follow up on our previous conversation. If yes can you please provide the sample data so that we can provide you with the accurate and correct solution. 

    Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

     

    Thank you.

    • niemis's avatar
      niemis
      Helper I
      1 year ago

      Hello.  Thank you.  I don't recall seeing a previous conversation, but am hopeful to find an answer.  Here is a table that I am working with.  Unfortunatley, I cannot share a pbix file for confidential and security reasons.  

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
      • Ashish_Excel's avatar
        Ashish_Excel
        Solution Supplier
        1 year ago

        Hi,

        Based on the table shared by you, show the expected result.

    • niemis's avatar
      niemis
      Helper I
      1 year ago

      v-sgandrathi thank you.  This is what I'm trying to accomplish: 

       

      In this table, for the month of April, I am trying to show the highest number of tickets with highest SLA Achievement.  And in contrast, the highest number of tickets with the lowest SLA Achievement.  The Assignment group isn't static; if I chose another department they could have more or less Assignment Groups.  

       

      MonthDepartmentAssignment GroupTotalSLA Achievement
      25-AprDevelopment BaT SW12373.17%
      25-AprDevelopment BSE Compute10176.24%
      25-AprDevelopment BSE Storage8068.75%
      25-AprDevelopment Componentization2458.33%
      25-AprDevelopment DevApps Nagios9694.79%
      25-AprDevelopment Testing IA2572.00%
  • 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