Forum Discussion

DarrenLau's avatar
DarrenLau
Advocate I
4 years ago
Solved

Filtering a slicer (no relationship_

Hi,

 

Looking for some help to see if this can be done. Conceptually, I need to filter a dimension table based on a selection on another dimension table. These two tables are unable to be linked together as one of relationships will be made inactive.

 

In the PBIX example attached, I want the Location slicer selection to filter the Salesperson slicer and show only the 5 salespersons that are in that location.

 

Example PBIX file 

Need Help
  • DarrenLau's avatar
    DarrenLau
    4 years ago

    Thanks Jihwan_Kim 

     

    That would be an acceptable solution if I am trying to find salespeople with sales, the slicer should show users filtered by location.

     

    I found the solution in another post and I have updated the PBIX - Filtering a slicer example 

     

    DAX used

    Location Filters Users =
IF (
    MAX ( 'Salespeople'[Location] )
        IN VALUES ( 'Sales Location'[SalesLocation] ),
    1,
    0
)

     

     

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    DarrenLau check this video on my YouTube channel which talks about something similar. https://youtu.be/XXVRwvni4Ms

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    • DarrenLau's avatar
      DarrenLau
      Advocate I
      4 years ago

      Hi parry2k ,

       

      Thanks for the video link. I couldn't work out how to utilise the CROSSFILTER function in my example, but I found another post that had the correct solution.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    1. create a measure like below.

    filter name by location: =
    IF ( COUNTROWS ( VALUES ( Sales[Value] ) ) > 0, 1, 0 )
    2. Put this measure into the Filterpane "Filter on this visual"
    3. configure like the picture.
     
     
    • DarrenLau's avatar
      DarrenLau
      Advocate I
      4 years ago

      Thanks Jihwan_Kim 

       

      That would be an acceptable solution if I am trying to find salespeople with sales, the slicer should show users filtered by location.

       

      I found the solution in another post and I have updated the PBIX - Filtering a slicer example 

       

      DAX used

      Location Filters Users =
IF (
    MAX ( 'Salespeople'[Location] )
        IN VALUES ( 'Sales Location'[SalesLocation] ),
    1,
    0
)