Display values from column A where all values from a slicer are present in column B.

[Anonymous]

You can think of it as a search engine, where you want results that contain all the tags you're looking for.

Let's say I have a table with columns filename, tag. For example:

Shop.png, building

Shop.png, outdoors

Shop.png, urban

Chair.png, indoors

Chair.png, furniture

Lawnchair.png, outdoors

Lawnchair.png, furniture

Sofa.png, indoors

Sofa.png, furniture

Etc.

I also used power query to get a table with distinct values from the tag column to use as a slicer.

Now normally if I selected on my slicer furniture and outdoors I'd get all files in my table, I'd like to only get those that have both of those tags, so in this case just the lawnchair.

I thought of simply putting in a calculated column that'd have a count of how many times each filename occurs in the table and then filter so only items where that number equals the number of selected slicer options would show, but I must've done something wrong because the numbers didn't seem correct or they just wouldn't change as the slicer filter applied, so it didn't work.

I don't know what to do, I'm honestly out of ideas and I've been searching for days.

I'd be eternally grateful for help.

[amustafa]

Sure, Here are the explanantions of the DAX measures I created.

AllTagsPresent =
Imagine you're working with a large collection of files, each tagged with various keywords or labels (tags) to describe its content or characteristics. This DAX formula helps determine which files have all of a given set of selected tags.

1. SelectedTags: This part creates a list of the specific tags you're interested in. Think of it as making a shopping list of ingredients you need for a recipe.
2. TotalSelectedTags: Counts how many items are on your shopping list. If you need flour, sugar, and eggs, your count is 3.
3. FileTags: For each file in your collection, this finds all the tags attached to it. If we stick with the recipe analogy, this is like checking the pantry for what ingredients you have for each recipe.
4. MatchingTags: This step checks which tags from your "shopping list" (SelectedTags) also appear in each file's list of tags (FileTags). Using the recipe analogy, this is finding out which recipes you can fully or partially cook based on the ingredients you have.
5. CountMatchingTags: Counts how many of your needed tags (ingredients) match with the tags on each file (recipe).
6. Return Value: If the number of tags that match (ingredients you have) equals the total number of tags you're looking for (all the ingredients needed for the recipe), then this file (recipe) is marked with a 1, meaning "Yes, this file has all the tags you're interested in." If not all tags match, it's marked with a 0, meaning "No, this file doesn't have all the tags you're looking for."

So, in essence, this formula is like going through a cookbook to find which recipes you can make based on the ingredients you already have. Each file is a recipe, and the tags are ingredients. The formula checks each "recipe" to see if you have all the "ingredients" you need, marking those you can fully make with a 1 and those you can't with a 0.

CountOfFilesWithAllTags =
This DAX formula works closely with the first measure AllTagsPresent. It's designed to count how many files in your collection meet a specific criteria: having all of a selected set of tags. Here's a simpler way to understand it:

1. VALUES(Files[filename]): First, imagine you have a list of all the file names in your collection. This part of the formula gets every unique file name, ensuring each file is considered just once. Think of it as making a guest list for a party where you want to invite every friend once, no duplicates.
2. [AllTagsPresent]: This references the previous formula we discussed. For each file, it checks whether all the selected tags are present. If a file has all the tags, it's marked with a 1 ("Yes, this one's perfect!"), and if not, it's marked with a 0 ("No, this one's missing something.").
3. SUMX(..., [AllTagsPresent]): Now, for each file in your list (every guest invited to your party), the formula adds up all the 1s and 0s from the previous step. Essentially, it's like going through your guest list and counting how many confirmed they have everything needed for the party theme (let's say, a costume party where each costume requires specific items).

So, in essence, this formula is counting how many files (or guests) are "perfect matches" for your criteria (party theme). If you wanted to know how many files have all the tags you're looking for, this formula gives you that exact number. It's a way of quantifying how many items in your collection fully meet your specified conditions.

[sevenhills]

All Selected Tags = if(ISFILTERED('Tags'[tag]),DISTINCTCOUNT('Files'[tag]),0)

See if this helps!

check this for more info: https://p3adaptive.com/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Anonymous]

If I only have the filenames in the table the measure will show 2 next to lawnchair, do you know how I could filter the table so it'd only show those values where that measure would equal countrows of tag table?

[sevenhills]

You can try replacing with countrows, count, counta, countax, countx ... depending on your needs.

@amustafa has provided the sample data, solution and .pbix file. See if that works!

Your have provided the source sample data. Similarly, If you provided the output expecting, then I can work on DAX. Tip: You can use that and provide the output expected. Or simple Excel copy paste or simple lines.

🙂

[Anonymous]

Apologies, I couldn't check it at the time, because the version of power BI at work was too old to open it. I am now home and at a quick glance amustafa's solution does seem correct!

As for the desired output I did write it.

Now normally if I selected on my slicer furniture and outdoors I'd get all files in my table, I'd like to only get those that have both of those tags, so in this case just the lawnchair.

---

In essence the problem was to select as many tags as I wanted and only see the files that contain all of the selected tags on a table. To implement a search/filter by tag in power BI.

I'll be honest, I don't fully understand amustafa's solution, but I'm tired and just glad to see it work.

I appreciate your help, thank you.

[sevenhills]

Glad to hear the solution provided by @amustafa works! He wrote a detailed explanation, which is cool!

[amustafa · Accepted solution]

Hi, see the Solution in the attached .pbix file.

[Anonymous]

Hello Amustafa,

Thank you for the solution, I got everything working and the question is solved. If you wouldn't mind, I'd love to make another request of you, could you help me understand your DAX code?
I find it hard to understand how the measure displays different values in different rows, as to my understanding measures don't evaluate row values/context, but entire tables. It's kind of hard for me to follow all in all.

So in the measure AllTagsPresent =
VAR SelectedTags = VALUES('Tags'[tag]) -- This is a single column of unique/distinct values in [tag], but it takes filters into account, so when slicers are applied it returns just the ones selected by slicers.
VAR TotalSelectedTags = COUNTROWS(SelectedTags) -- This is a count of rows in that table (so in this case 1? Since after the slicers, only 2 rows with the same value "Lawnchair.png" should be there?),
VAR FileTags = CALCULATETABLE(VALUES(Files[tag]), ALLEXCEPT(Files, Files[filename])) -- this is a table of all distinct values of [tag] in table Files, but it removes all filters other than/except the ones applied to the column [filename] (but are there any?) - so it'd be a table of... all possible values of tag? Is that right? Why not just do Tags[tag] then? Is the filter in CALCULATETABLE applied to the "VALUES()" table or the original Files[tag] table before the VALUES function does its thing? Becaue if it's applied after values(), then wouldn't it just be the same as selectedtags, since the table after values contains only one column? Idk.
VAR MatchingTags = INTERSECT(FileTags, SelectedTags) -- This picks out the rows from FileTags that are also in SelectedTags I think? Wouldn't that just be the same as SelectedTags then?
VAR CountMatchingTags = COUNTROWS(MatchingTags) 
RETURN IF(CountMatchingTags = TotalSelectedTags, 1, 0)

The other measure 
CountOfFilesWithAllTags = 
SUMX(VALUES(Files[filename]), [AllTagsPresent]) -- Does this sum the amount of distinct filenames after filters - so the ones selected, to the AllTagsPresent measure? Does this change the value of AllTagsPresent? Because if it doesn't, I don't understand why this measure is necessary for everything to work. It doesn't seem to influence the other measure, and it's not used as a filter. I'm confused.

Thank you for your patience.

[lbendlin]

Use the advanced filter options of the filter pane. You can have up to two OR'ed conditions on the same column