Filter out multiple connected rows based on the data in one row

[dbltnk]

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

[nadinelizabeth]

Is there a way to do this in power query editor?

[v-ljerr-msft · Accepted 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

[dbltnk]

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