Filter To Show All With Atleast 1 Match

DAX DAX Calculations
1

Hi,

I have a filter to select fruits. When selecting the fruit (Eg:- Apple) it filters the table accordingly. What I really want to be able to do is if Appleis selected, find the clients that purchased Apple and show all products purchased for those matching clients.

In this case I want the table to shows all of Joe’s purchases (Apple & Orange) as he purchased apple (See highlighted in attached pic for expected output).

Would appreciate if someone point me as to which of the training modules or post has something similar as I would like to try and solve it myself and then post back if i run into any issues.

Thanks
Capture

2

Hi there,

That’s not how filter works. if you select apple in filter that what you get that only have apples.

It works the same way if you do it in excel.

i guess i’ll keep my comments to myself

thanks
Keith

3

@adsa,

Kudos to you for wanting to work this out on your own. Let me try to drop a few breadcrumbs that might put you on a useful trail:

Search the forum for “disconnected table”

https://info.enterprisedna.co/dax-function-guide/contains/

Depending on how you structure your solution, this might also be a useful tool in your toolbox:
https://info.enterprisedna.co/dax-function-guide/concatenatex/

I hope these prove helpful. Good luck!

  • Brian
4

@BrianJ Thanks for breadcrumbs. I had play with the 'CONCATENATEX " function and this will not work for me as the values all fit within a cell whereas i need each of those values to be separated out into individual rows.

The contains function worked a treat. I was able to join the disconnected tables with that function. However i still cannot figure out how to obtain all transactions for ‘Joe’ (i.e- Apples & Orange) if Apple is selected.

The only other possible solution is if Apple is selected in the filter, then I want to separate out all the unique Client and then use countrows with values to match the client names and pull out every transaction but I can’t seem to obtain a set of unique filtered client names as there are duplicates.

Here’s a sample copy of PBIX
Sample.pbix (29.0 KB)

5

@adsa,

Glad you found that helpful. By the way, CONCATENATEX will work – you just need to combine it with the code for hard return, UNICHAR(10). In the link below, you’ll find a bunch of posts where I use this combination to return a scalar that actually looks like a multiline table. It’s an incredibly useful and flexible combination:

https://forum.enterprisedna.co/search?q=Concatenatex%20unichar

I’ll take a look at your other issue and hopefully get back to you soon with a proposed solution.

– Brian

6

@adsa,

Lot of different ways you could go to get to a solution on this one. Here’s the path I took using a
disconnected table slicer and the IN operator.

Note: I built this to allow multi-select in the slicer, but it still works even if you use only single select.

Keep Buyers = 

VAR SelDisconnFruit = VALUES( 'Disconn Fruit'[Brand] )
VAR SelClient = SELECTEDVALUE( Data[Client] )

VAR Buyers =
CALCULATETABLE(
    DISTINCT( Data[Client] ),
    Data[Fruit] IN SelDisconnFruit
)

VAR BuyerIn =
IF( SelClient IN Buyers, 1, 0 )

RETURN
BuyerIn

image

image

I hope this is helpful. Full solution file attached below.

– Brian
eDNA Forum – Disconnected Client Selection Solution.pbix (29.6 KB)

7

@BrianJ Thank you so much for your assistance. Much appreciated!

Exactly what I was after!

8

@adsa,

My pleasure – glad that worked well for you. You always have interesting questions, and this one had a number of aspects I want to delve a bit deeper into, so I’m in the process of making a video based on it. Look for it within the next couple of weeks on the Enterprise DNA YouTube channel.

  • Brian
9

@BrianJ Brilliant stuff!! I look forward to it & Thank you once again!

10

@adsa,

FYI - the video inspired by your question posted today.

Thanks!

  • Brian
1 Like
11

Thanks @BrianJ much appreciated!!

1 Like