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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors