Forum Discussion

moon_walker's avatar
moon_walker
Icon for Helper I rankHelper I
4 years ago
Solved

Excluding irrelevant rows from filtering

Hello,

I have a dataset coming from our enterprise data source and I use SQL to query it. 

It looks like this:

 

Ticket NumberComponentParentMarkerExecutive
TICK0001server1other1  
TICK0001server1app1XPeter Parker
TICK0002server2app2XTony Stark
TICK0003server3   
TICK0004server4other2  
TICK0004server4app3XPeter Parker
TICK0005server5other3  
TICK0006server6app4XTony Stark
TICK0006server6app5XTony Stark
TICK0006server6other4  

 

Each ticket has a server associated to it. Servers can be related to an application as well as some other 'parent' system (see TICK0001). And some servers have no parents (see TICK0003). And some servers can be associated to multiple apps (see TICK0006). Also some servers could only have an "other" as the "parent" system (TICK0005).

 

I need to be able to report on all tickets and be able to filter for tickets that have an application associated, and those that don't.

For example, I need to be able to

1) display the tickets under an executive (Peter's or Tony's tickets), and, 

2) at the same time, display the tickets that don't have an app as the parent system (TICK0003 and TICK0005). The issue is that Power BI starts filtering for the more irrelevant rows from TICK0001, TICK0004, etc. the entries with 'other' in it. 

 

Is there something Power BI can do, or should I alter my SQL query?

Any help would be appreciated.

Thanks.

Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi moon_walker 

     

    You can add two new columns to the original table

    New Executive = CALCULATE(MAX('Table'[Executive]),ALLEXCEPT('Table','Table'[Ticket Number]))
    New Parent = 
var vParent = 'Table'[Parent]
var vAllParent = CONCATENATEX(CALCULATETABLE(VALUES('Table'[Parent]),ALLEXCEPT('Table','Table'[Ticket Number])),'Table'[Parent],",")
return
IF(vAllParent="","No Parent",IF(NOT(CONTAINSSTRING(vAllParent,","))&&CONTAINSSTRING(vAllParent,"other"),"No Parent",'Table'[Parent]))

     

    Use new columns in the visuals. 

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi moon_walker 

     

    You may need to design how to display the expected result in the report first. If you want to display all executives' tickets in a visual at the same time, the solution will be different from displaying only an executive's tickets at a time. Also, if you switch to different visuals, the solutions may need to be modified. 

     

    Based on the example requirement, you can create a measure as a flag. In below image, I put the measure on the left table visual, you can see it only displays 1 for TICK0003 and TICK0005. On the right table, I add this measure to its visual-level filter pane and set it to show items when value is 1. Then you will only see both ticket numbers that meet the need. 

     

    I see that some tickets don't have an executive in your sample data. Is this normal? If so, when you add executive column to a visual, it will group all tickets without executive into a group with executive "blank". For example, TICK0006 will appear under both Tony Stark and blank. How do you want to display it in this scenario? 

     

    I attached my sample file at bottom. Hope it helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • moon_walker's avatar
      moon_walker
      Icon for Helper I rankHelper I
      4 years ago

      Hello v-jingzhang 

      Thank you for the reply. I will investigate to see how I can use this.

      Yes it is normal for some tickets to not have any executive. Like TICK0003 and 0005. 

      For TICK0006, I would want it to show only under Tony Stark, not under 'blank' too.

       

      So in the below picture, those two pie slices should be combined. And components like other1, other2, and other4 should not show up at all. (I am manually filtering for these slices at the moment)

       

       

      Thank you for all your help!

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi moon_walker 

         

        You can add two new columns to the original table

        New Executive = CALCULATE(MAX('Table'[Executive]),ALLEXCEPT('Table','Table'[Ticket Number]))
        New Parent = 
var vParent = 'Table'[Parent]
var vAllParent = CONCATENATEX(CALCULATETABLE(VALUES('Table'[Parent]),ALLEXCEPT('Table','Table'[Ticket Number])),'Table'[Parent],",")
return
IF(vAllParent="","No Parent",IF(NOT(CONTAINSSTRING(vAllParent,","))&&CONTAINSSTRING(vAllParent,"other"),"No Parent",'Table'[Parent]))

         

        Use new columns in the visuals. 

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.