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 inc...
  • 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

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