topic Re: want to select values that are NOT in the slicer selection in DAX Commands and Tips

want to select values that are NOT in the slicer selection

kenmuir71 — Thu, 16 May 2024 09:06:18 GMT

I have a data table called GamesNotOnASite , example of data below;

GameID	SiteID	CountryID
10	1	1
10	5	1
10	7	2
20	1	2
20	5	1
20	7	2
30	9	3
30	11	3

I want a viusal table or Matrix which has GameID's. I want two slicers, CountryID and SiteID. I want the CountryID to select the GameID's, I want the SiteID slicer to do the opposite, selecting a SiteID will show GameID's that are NOT in SiteID, but in the CountryID.

Using the data example above I would get the following results;
When countryID = 1
when SiteID = 1
Visual should show Game ID 20

When CountryID = 2
when siteID = 1
Visual would show GameiD = 10

Anyone know how to do this? I want to use measures as the actual table is 0.5Million rows

Thanks!

Re: want to select values that are NOT in the slicer selection

mark_endicott — Thu, 16 May 2024 16:02:42 GMT

kenmuir71 - I've made the assumption that all 3 of your columns are in the same table. But to do this, you will need to create an additional disconnected table, containing only the SiteIDs.

By disconnected I mean make sure there is no relationship. You can do this easily in Power Query, or through creating a Calculated Table with the DAX:

VALUES( 'Table (3)'[SiteID] )

Once you have done this, you can create the following measure:

VAR _game_id_Site = VALUES ( 'Table (3)'[SiteID] ) VAR _site_id_Site = VALUES ( 'site id'[SiteID] ) VAR GameIDs_not_in_site_ID = EXCEPT ( _site_id_Site, _game_id_Site ) RETURN COUNTROWS ( GameIDs_not_in_site_ID )

You can then add this measure to your Table filters and set to "is not blank", when you set your slicers up, use the SiteID from the disconnected table.

If this solution works for you, please mark it as the solution.

Re: want to select values that are NOT in the slicer selection

kenmuir71 — Thu, 16 May 2024 17:41:10 GMT

Hi Mark

Thanks for the help, its appreciated. The solution works partially, however for the second example it gives GameID 10 & 20. It should only be GameID 10, As GameID 20 is present on site 1 and 7, so should be excluded. Is this easily resolved?

When CountryID = 2
when siteID = 1
Visual would show GameiD = 10

Re: want to select values that are NOT in the slicer selection

mark_endicott — Fri, 17 May 2024 08:23:00 GMT

kenmuir71 - I'm afraid you'll need to look at the way you have set this up (check the data, relationships, DAX, filters etc).

The result you are seeing is not reflected in mine. I have your expected result:

Re: want to select values that are NOT in the slicer selection

mark_endicott — Fri, 17 May 2024 08:23:47 GMT

kenmuir71 - I would be grateful if you could mark this as the solution given I have proven it works.

Re: want to select values that are NOT in the slicer selection

kenmuir71 — Fri, 17 May 2024 08:58:35 GMT

my mistake, it works now. Thanks for your help!

Re: want to select values that are NOT in the slicer selection

mark_endicott — Fri, 17 May 2024 09:49:40 GMT

kenmuir71 - Happy to help!