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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kenmuir71
Helper I
Helper I

want to select values that are NOT in the slicer selection

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

GameIDSiteIDCountryID
1011
1051
1072
2012
2051
2072
3093
30113



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!

1 ACCEPTED SOLUTION

@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:

 

mark_endicott_0-1715933991356.png

mark_endicott_2-1715934073237.pngmark_endicott_3-1715934090539.png

mark_endicott_5-1715934145103.png

 

View solution in original post

6 REPLIES 6
mark_endicott
Super User
Super User

@kenmuir71 - Happy to help!

kenmuir71
Helper I
Helper I

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

mark_endicott
Super User
Super User

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

mark_endicott
Super User
Super User

@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. 

 

mark_endicott_0-1715875328186.png

 

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

@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:

 

mark_endicott_0-1715933991356.png

mark_endicott_2-1715934073237.pngmark_endicott_3-1715934090539.png

mark_endicott_5-1715934145103.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors