Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to filter the non-matched values?

Hello

 

In the pbix below, I have a slicer to filter a table visual however, I cannot filter the lines where the ColB2 is blank.

 

Any idea?

 

https://ufile.io/h5aqx22j

 

Thanks!

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hello @UsePowerBI ,

    This is because you have created many-to-many relationships in the sample file. In this relationship, the field value 1,2,3,4 in ColA1 has no corresponding value in ColB1 so that it is not filtered when you use ColB2 as the slicer.

    You can create a bridge table that includes the ColA1 and ColB1 values and establish a relationship with the other two tables respectively to avoid the many-to-many relationship, set the visual filter of the table visual, because ColA1 is not blank, you can now use ColB2 segmentation to filter the blank value normally :

    many to many.pngfilter.pngccol.png

    Attached the modified sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous I have seen people use Append query in Power Query or UNION in DAX to add a blank row to their table so that they have a match?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

    Seeing both tables have the same structure, you should append them (preferebaly in Power Query) to create  a single table with the three columns.

     

     

    You can then create dimension tables to do your slicers/filtering/filter expressions etc...

     

    I've attached the PBIX file for your reference

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hello @UsePowerBI ,

    This is because you have created many-to-many relationships in the sample file. In this relationship, the field value 1,2,3,4 in ColA1 has no corresponding value in ColB1 so that it is not filtered when you use ColB2 as the slicer.

    You can create a bridge table that includes the ColA1 and ColB1 values and establish a relationship with the other two tables respectively to avoid the many-to-many relationship, set the visual filter of the table visual, because ColA1 is not blank, you can now use ColB2 segmentation to filter the blank value normally :

    many to many.pngfilter.pngccol.png

    Attached the modified sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-yingjl  thanks, can you please repost the pbix after fixing the error: Object Reference not set to an instance of an object

      I cannot open the pbix.

      Or you can paste here the code to create the bridge table please?

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi Anonymous ,

        Sorry for replying late. Maybe the version of power bi desktop is not matched. Perhaps you can use the latest version of power bi desktop to try to open it.

        In addition, the bridge table in this case is created manually( just enter data ).

        Or you can create this bridge table by this formula:

        Table = GENERATESERIES(1,12,1)

         

        Best Regards,
        Yingjie Li

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.