Forum Discussion

dbltnk's avatar
dbltnk
Frequent Visitor
9 years ago
Solved

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

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

    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

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

    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's avatar
      dbltnk
      Frequent Visitor
      9 years ago

      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