Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody,
I am currently analyzing log data from a two-player multiplayer strategy game. My data is distributed over multiple tables of a database. One table shows me if a game session included AI characters or not.
This is how the table looks like: https://www.dropbox.com/s/geuuoxhyc4auets/rep1.png?dl=0
As you can see, each game session exists twice in the table, once for each player.
Now I want to filter out all game sessions where ANY of the players is an AI. But if I use the AI column as a filter for my report then only the rows including the AI player get filtered out. And I want to filter out BOTH the AI and the human player.
How can this be done?
Thanks!
dbltnk
Solved! Go to Solution.
Hi @dbltnk,
According to your description above, you can use the formula below to create a new calculate column in the table first, then you should be able to use this new created calculate column "is_ai" to filter out all game sessions where ANY of the players is an AI.
is_ai = IF ( CALCULATE ( SUM ( Table1[ai] ), FILTER ( ALL ( Table1 ), Table1[session_id] = EARLIER ( Table1[session_id] ) ) ) >= 1, 1, 0 )
Note: You need replace "Table1" with your real table name.
Regards
Is there a way to do this in power query editor?
Hi @dbltnk,
According to your description above, you can use the formula below to create a new calculate column in the table first, then you should be able to use this new created calculate column "is_ai" to filter out all game sessions where ANY of the players is an AI.
is_ai = IF ( CALCULATE ( SUM ( Table1[ai] ), FILTER ( ALL ( Table1 ), Table1[session_id] = EARLIER ( Table1[session_id] ) ) ) >= 1, 1, 0 )
Note: You need replace "Table1" with your real table name.
Regards
Thanks so much - that worked flawlessly for me and I could even adapt it to a couple other filters.
Though to be honest I do not yet fully understand what it does - the nested functions in DAX are a bit too confusing for my brain. =D
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |