Forum Discussion

MadisMerk's avatar
MadisMerk
Icon for Helper I rankHelper I
5 years ago

Filter on data that does not exist in a table

Hi,   I have two tables in my model:   ID Name 1 Company A 2 Company B 3 Company C 4 Company D 5 Company E 6 Company F 7 Company G 8 Company H 9 Company I ...
Need Help
Tips and Tricks
MadisMerk's avatar
MadisMerk
Icon for Helper I rankHelper I
5 years ago

Hi,

 

It does work partially, but I would like to use it in a slicer and also have the rest of the transport regions present in the same slicer.

 

 

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

Hi MadisMerk ,

 

First create a column in company table:

_region = RIGHT('Table'[Name],1)

Then create a measure as below:

Measure = IF(NOT(MAX('Table'[_region]) in FILTERS('Table (2)'[Transport Region])),1,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!

 

  • MadisMerk's avatar
    MadisMerk
    Icon for Helper I rankHelper I
    5 years ago

    Hi,

     

    This is the other part of the equation, but how am I able to use it in a slicer?

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

      Hi MadisMerk ,

       

      Create a slicer table as below:

      slicer table = VALUES('Table (2)'[Transport Region])

      Then create a measure as below:

      Measure = 
var _tab=CALCULATETABLE(VALUES('slicer table'[Transport Region]),ALLSELECTED('slicer table'))
Return
IF(MAX('Table'[_region]) in _tab,BLANK(),1)

      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!

      • MadisMerk's avatar
        MadisMerk
        Icon for Helper I rankHelper I
        5 years ago

        Hi,

         

        Would it also be possible to have a "blank" option in the slicer?

         

        Madis