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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Responsive Resident
Responsive Resident

@kenmuir71 - Happy to help!

kenmuir71
Helper I
Helper I

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

mark_endicott
Responsive Resident
Responsive Resident

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

mark_endicott
Responsive Resident
Responsive Resident

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.