Forum Discussion

Ianc33's avatar
Ianc33
Frequent Visitor
6 years ago
Solved

Remove rows matching slicer output

Hi Everyone,

 

I have a table which is similar to the below.

 

NAME, TAG

name1,tag1

name1,tag2

name2,tag1

name2,tag2

name3,tag1

name3,tag2

name4,tag1

 

Which is in a table visual.

 

If I was to slice the visual on tag2, i want another table visual that contains only name4 as it doesn't have tag2.

 

Is this possible?

 

Thanks in advance for your help.

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Ianc33 ，

     

    Duplicate the table;

    Then create a measure as below:

    Measure = 
var _namesexclude=CALCULATETABLE(VALUES('Table 2'[NAME]),FILTER(ALL('Table 2'),'Table 2'[TAG]=SELECTEDVALUE('Table'[TAG])))
var _nameremain=EXCEPT(DISTINCT('Table 2'[NAME]),_namesexclude)
Return
IF(ISFILTERED('Table'[TAG])=FALSE(),MAX('Table 2'[NAME]),IF(MAX('Table 2'[NAME]) in _nameremain,MAX('Table 2'[NAME]),BLANK()))

    And you will see:

    For the related .pbix file,pls see attached.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

4 Replies

    • Ianc33's avatar
      Ianc33
      Frequent Visitor
      6 years ago

      Hi Vanessafvg,

       

      Thanks for taking the time to reply.

       

      What I'm looking for is,

       

      1 table visual that shows the matching data and a second that shows the non-matching data from the slicer

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Ianc33 ,

    You can get a table like

    table = except(all(Table1[Tag]), allselected(Table1[Tag]))

     

    or can get measure and add it visual

    measure = count(countrows(Table), filter(all(table), not(Table1[Tag] in values(Table1[Tag]))))

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Ianc33 ，

     

    Duplicate the table;

    Then create a measure as below:

    Measure = 
var _namesexclude=CALCULATETABLE(VALUES('Table 2'[NAME]),FILTER(ALL('Table 2'),'Table 2'[TAG]=SELECTEDVALUE('Table'[TAG])))
var _nameremain=EXCEPT(DISTINCT('Table 2'[NAME]),_namesexclude)
Return
IF(ISFILTERED('Table'[TAG])=FALSE(),MAX('Table 2'[NAME]),IF(MAX('Table 2'[NAME]) in _nameremain,MAX('Table 2'[NAME]),BLANK()))

    And you will see:

    For the related .pbix file,pls see attached.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!