Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PieMashandGravy
Regular Visitor

Filter an unrelated table with one of two values in a slicer, including output where both match?

Hi - newbie here, having some partial success with a DAX issue but need some help getting over the line. 

 

I've changed and simplified my data to be able to post it here. I have a 'Stores' table, listing different types of stores in two cities (London and Manchester), which needs to be filtered by an unrelated slicer (for conditonal formatting reasons). The unrelated slicer table, 'Cities',  contains the two cities. 

Stores = {("London","Barber"),("London","Newsagent"),("London","Outdoor Store"),("London","Boutique"),("London","Dry Cleaner"),("London","Convenience Store"),("London","Chippy"),("London","Pawnbroker"),("London","Jeweller"),("Manchester","Barber"),("Manchester","Newsagent"),("Manchester","Vape Store"),("Manchester","Dry Cleaner"),("Manchester","Convenience Store"),("Manchester","Upholsterer"),("Manchester","Opticians"),("Manchester","Jeweller"),("Manchester","Chippy"),("Manchester","Kebab Shop")}

 

PieMashandGravy_0-1689505514944.png

 

Cities = {("London"),("Manchester")}
 

PieMashandGravy_1-1689505618376.png


I am trying to make the filter on the table work by putting a 0/1 condition in, off the back of a measure, which my intention is to return a 1 for a match on the city, and to pull back all stores with a 1. This measure then goes into the visual-level filter for the list of stores.

I can make it work with a super-simple measure comparing the MAX(Stores[City]) and the SELECTEDVALUE of the Cities slicer;

StoreList =
    IF (
        MAX(Stores[City]) = SELECTEDVALUE(Cities[City]),
        1,
        0
    )


However, the MAX component is tripping me up returning everything for Manchester, but only returning the London stores where they don't exist in Manchester. What I am looking to do is to adapt my DAX so that it returns a 1 for all rows where there is a match on the city, irrespective of whether there's a match on the other city or not, which I believe involves circumventing the use of aggregation on the Stores table to do the check.

PieMashandGravy_2-1689505891772.png

PieMashandGravy_3-1689506091744.png

 

Massive thanks in advance for any suggestions - I've tried all sorts of things (IN, CONTAINS, FIND, FILTER etc) but I cannot make it work.

1 ACCEPTED SOLUTION

@PieMashandGravy 
Please try

StoreList =
INT (
    NOT ISEMPTY ( FILTER ( Stores, Stores[City] IN VALUES ( Cities[City] ) ) )
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @PieMashandGravy 

please caleify the following 

"What I am looking to do is to adapt my DAX so that it returns a 1 for all rows where there is a match on the city, irrespective of whether there's a match on the other city or not"

Hi @tamerj1 

So basically what I mean is, I want the measure to retrun a 1 for all values where the City matches, and not just the MAX. For example, in my above data, London has a Convenience Store, a Jeweller and a Dry Cleaner, which I think aren't being returned as 1 because Manchester also has them, and Manchester is the MAX value. What I am looking for is for Convenience Store, Jeweller and Dry Cleaner to be returned as 1 for both London and Manchester.

This all comes down to how I am writing the DAX in the Measure - I am using MAX because that's how I learnt to create measures which return values that can be filtered on. I think my question reduces down to how to create this measure differently, so that it does what SQL would do with SELECT * FROM Stores WHERE City = 'London'

Hope that makes sense. 

@PieMashandGravy 
Please try

StoreList =
INT (
    NOT ISEMPTY ( FILTER ( Stores, Stores[City] IN VALUES ( Cities[City] ) ) )
)

Thank you @tamerj1, you are a legend! That works like a charm. I have no idea how long I would have been trying to figure that out for myself - my massive gratitude!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.