Forum Discussion

moon_walker's avatar
moon_walker
Helper 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 Number Component Parent Marker Executive TICK0001 server1 other...
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.

v-jingzhang's avatar
v-jingzhang
Community 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
Helper 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
    Community 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.