topic How to use multiple selections in slicer to show results containing ALL of those values in DAX Commands and Tips

How to use multiple selections in slicer to show results containing ALL of those values

swagga90 — Thu, 11 Jul 2024 15:41:43 GMT

Hello all,

I am fairly new to PowerBi and I'm working on a dashboard that takes selected jobs and shows the different procedures that contain those jobs. However, when selecting multiple jobs, the resulting table produces procedures that contain 1 or all of the selected jobs, and I would like for the table to show results of procedures that at minimum contain all of the selected jobs (essentially it's a filter that would filter based on an AND function of the selected jobs). Furthermore, I'd like to make a separate filter that shows results that only contain those selected jobs and not more.

Here is an example of the two tables I'd want:

Selected Jobs: Job A, Job B, Job C

Table 1 with Filtered Results should show:

Procedure 1: Job A, job b, job c, job d

Procedure 2: Job A, Job B, Job C, Job D, Job F, jOB Z

Procedure 3: Job a, job b, job c

Table 2 with Filtered Results:

Procedure 3: job a, job b, job c

Currently, my table is producing results such as:

procedure 1

procedure 2

procedure 3

Procedure 4: job a

procedure 5: job a, job c, job d

Which contain all combinations of any of the selected jobs, but don't show the procedures that at minimum have ALL the selected jobs.

Any thoughts on how to tackle this?

Many thanks.

Iwona

Re: How to use multiple selections in slicer to show results containing ALL of those values

GuillaumePower — Thu, 11 Jul 2024 16:12:49 GMT

Hey, Could you show your data model ? I'm not sure that you have only one table or two?

Could you show us your screen ?

It is very easy to copy your screen here.

As you see below :

Re: How to use multiple selections in slicer to show results containing ALL of those values

swagga90 — Thu, 11 Jul 2024 20:01:18 GMT

It is one table that contains many columns. The two columns of interest are Job and Procedure. For each procedure, there can be a number of jobs, and so, each procedure will have n number of rows depending on what jobs are associated with it. In terms of the visual/page: The slicer uses the available job options from this table. The table visual gets filtered based on what jobs you select.

Re: How to use multiple selections in slicer to show results containing ALL of those values

GuillaumePower — Fri, 12 Jul 2024 09:24:59 GMT

There is certainly a mistake in your data model, you should show everything on the screen to find what dosn't work. You can hide the data that you don't want share but we can't help whithout a screenshot.

Re: How to use multiple selections in slicer to show results containing ALL of those values

swagga90 — Fri, 12 Jul 2024 13:30:59 GMT

What screen are you hoping to see? The visual? The model between tables?

Re: How to use multiple selections in slicer to show results containing ALL of those values

GuillaumePower — Fri, 12 Jul 2024 14:19:02 GMT

Yes all the visuals and the table, (you can hide what you wan't not show). Think that we are in the same office, I would have to see your screen to really understand) Only 2 seconds to copy the text below in image.

Re: How to use multiple selections in slicer to show results containing ALL of those values

swagga90 — Fri, 12 Jul 2024 16:03:10 GMT

Here is a modified (I've removed some of the details) screenshot of the visual itself:

You select the jobs in the slicer at the top.

The table below it generates a list of procedures that contain those jobs. I've got it to the point that it displays the selected jobs as if it was an AND function, however, it shows procedures with the number of selected jobs as well as n-1. I used a measure that i found online for this AND-ONLY condition, but that example used two separate tables and i've been using only one table (data source) so perhaps something in that logic is flawed resulting in this odd behaviour.

In the example below, i have selected 6 jobs, and the resulting list shows a number of procedures. 1 of them has the 6 jobs in it and the other only have 5 of those 6. The other feature I'd like is for that table to show the total jobs in the procedure, regardless of the slicer selection. As you can see, to the table to the right, there are more than the 6 selected jobs. Is there a way to make a measure that calculates the total number of jobs irrespective of the filter/slicer applied? I've removed some details and changed names of some of the things for clarity, but I hope i didn't make it more confusing.

Here is a snapshot of the table (data), 'Standard Job proc':

And here is that measure that i used to filter the visualization with (it ends up being the column that says 'number of jobs matching selected job count'):

Re: How to use multiple selections in slicer to show results containing ALL of those values

GuillaumePower — Sat, 13 Jul 2024 16:04:33 GMT

I've begun to work with your code, I'll send you back tomorow a PowerBI file. But it s important to know exactly what you do with the Dax. You need one month at least to know well how to work with. (there is video course on udemy for the Dax)

Re: How to use multiple selections in slicer to show results containing ALL of those values

GuillaumePower — Sat, 13 Jul 2024 16:05:41 GMT

In fact your measure doesn't work because in your measure the selectedjobs is always equal to the countrowsprostjobs, so it's a non sens to compare this two value. I've tested this in a same example :

You'll have the same result for each proc, like below :

so it's a non sense to try to know when they are different :

if (countrowsprostdjobs>=countrowsjobs,countrowsprostdjobs,blank())

You can test this whith this powerbi : https://drive.google.com/file/d/1atacfNoV_bJ5GTH-RkqPSUzEz2tZESZM/view?usp=sharing

About what you have asked to clear the filter, you can use the Dax function Removefilter : you have to use something like : countrows(filter(Table),removefilter()),

A good advice, it to test every part of the measure that you re writing to find the right way to your duty.