Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbltnk
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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.

 

c1.PNG

 

Regards

View solution in original post

3 REPLIES 3
nadinelizabeth
New Member

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

v-ljerr-msft
Employee
Employee

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. Smiley Happy

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.

 

c1.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.