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.

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.