Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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:
my mistake, it works now. Thanks for your help!
@kenmuir71 - I would be grateful if you could mark this as the solution given I have proven it works.
@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.
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
29 | |
23 | |
22 | |
22 |