calculate measure with advanced tabel functions

[Anonymous]

Hi 😄

my end goal is to have a tabel with all unique games and a measure with all unique players. the measure need to change depending on the slicers value selected, but if a specific game name is selected then the tabel needs to keep all game names that has been played by one of the unique players calculated in the measure.  

example

player 1 is playing game 1, game 2 and game 3 in july 2023.

player 2 is playing game 2 and game 3 in july 2023.

and the power bi users filter the data in a slicer where game = game 1
the tabel has to return game 1, game 2 and game 3 becasue player 1 played that game but player 2 did not. 

we have to return all the games that a player played.

right now i only show the game name that i filter with all unique players that played that game. i need to show all the games that the unique players have played. not only Toby´s Tree.

to simplyfy there is one dimension called dim games and one fact tabel called fact Game. 

Unique Playing Users =

CALCULATE(

    DISTINCTCOUNT('Fact Game'[AccountId])

)

test = 

var _all = CALCULATE([Unique Playing Users],FILTER(ALL('Dim Game'), [Unique Playing Users] > 0 ))

var _uni = CALCULATE([Unique Playing Users],FILTER('Dim Game', [Unique Playing Users] > 0 ))

return

DIVIDE(_all,_uni)

[Anonymous]

Hi 😄

i solved it by creating a new fact tabel creating the nessary realtions ship and create a measure that activate bidirectional direction. 

[schneidej9]

To create such a table, you will need to use a database of information about games and players, and then go through several steps to obtain the information you need.

[Anonymous]

Hi @Anonymous ,

Here I create a sample to have a test.

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Year Month",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "MMMM YYYY" )
)

Game = VALUES('Table'[Game])

Data model:

Measure:

Filter =
VAR _SELECTGAME =
    SELECTEDVALUE ( Game[Game] )
VAR _LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[Game] ),
        ALLEXCEPT ( 'Table', 'Table'[Player], 'Table'[Month Year] )
    )
RETURN
    IF ( _SELECTGAME IN _LIST, 1, 0 )

Add this measure into visual level filter and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.