topic Re: Counting players that are part of a party that haven't done an action recently (or ever) in DAX Commands and Tips

Counting players that are part of a party that haven't done an action recently (or ever)

leste — Thu, 16 Feb 2023 17:28:40 GMT

Hi!

I am new to Power BI and I want to try to make a measure based on the following model:

I want to find is the count of players, that are wizards, that are in a party, and whose party didn't perform a given action (for instance "Find a treasure") recently.

A player should never be counted in these cases:

If the player is not in the "Wizard" family, then it's not a match
If the player does not belong to a party, then it's not a match

If a player is a wizard that belongs to a party, it should only be counted in these cases:

If the "Find a treasure" action is missing from the "Party_Actions" table, then the party has never performed the action, so it's a match
If the "Find a treasure" action is there, but there are no occurrences, then the party has never performed the action, so it's a match
If the action is there, but all occurrences happened more than 10 days ago, then it's a match

So far I've come up with the following DAX measure:

Players in that situation =
CALCULATE(
    CALCULATE(
        COUNTROWS(Players),
        // Action is "Find a treasure"
        KEEPFILTERS(Action_Types[Action Description] = "Find a treasure"),
        // There has been no action in the past 10 days
        COUNTROWS(
            FILTER('Action_Occurrences', DATEDIFF([Date], UTCTODAY(), DAY) >= 10)
        ) > 0
    ),
    // Players that are wizards
    KEEPFILTERS(Player_Families[Family] = "Wizard"),
    // And belong to a party
    NOT(ISBLANK(Players[Party ID])
))

But it doesn't work (I get errors), and I think that even if I could get it to work, it wouldn't match the cases I want (especially when the action doesn't exist, or when the action has no occurrences; in these cases, I want to count the player, but here I think it will ski

p the players).

Would you have any leads?

Re: Counting players that are part of a party that haven't done an action recently (or ever)

ValtteriN — Thu, 16 Feb 2023 20:52:50 GMT

Hi leste ,

This was quite fun issue to ponder. Here is one way to go about this:

1. I drew a flowchart to help me visualize the challenge:

2. Based on this I created the following dax:

Player Count = COUNTROWS( var _table = FILTER(ADDCOLUMNS(Action_occurrance,"Type", RELATED(Action_types[Description]),"Party",RELATED(Actions[PartyID])),and([Type]="Find Treasure",[Date]>=TODAY()-10)) return // variable table for invalid action occurances FILTER( ADDCOLUMNS( Players,"Family",RELATED(Player_family[Family]), "Party", RELATED(Parties[PartyId])), [Family]="Wizard" && not(ISBLANK([Party])) //Filter for wizards who have a party && not([Party] in { SELECTCOLUMNS(_table,"Party",[Party])}) //Parties who don't fill the conditions = have found treasure in 10 days. I am using Not in to remove the players who are in these parties from count ) )

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Counting players that are part of a party that haven't done an action recently (or ever)

leste — Fri, 17 Feb 2023 16:29:50 GMT

Wow, that's quite a formula!

It works like a charm, I just had to remove the curly braces { } around SELECTCOLUMNS. I also wrapped everything in a CALCULATE function, so that I could move one of the filters (not(ISBLANK([Party])) to that level (I've read it's a good practice so that filters are applied early).

The numbers seem to match the data I throw at the measure, so I think it's good! Thank you a thousand times!

I now know how many wizards haven't found treasures recently, maybe they need a little magic help too 🧙

Re: Counting players that are part of a party that haven't done an action recently (or ever)

leste — Wed, 22 Feb 2023 11:24:19 GMT

As a follow-up: my queries seemed slow, so I looked at them using DAX studio.

I found out Power BI was iterating over the full Action_Occurrences table, which has more than 100k rows in my dataset, so I changed the measure to (1) filter earlier and (2) avoid adding columns to that table.

The resulting query is a little more complex but consistently works 5 times faster with my data (from 2.5 seconds to 0.5 seconds to evaluate it) and yields the same results:

Treasureless Wizards = CALCULATE( COUNTROWS( // Variable table for invalid action occurrences (those we don't want) // All the "find treasure" actions that happened in the past 10 days VAR _Unwanted = SELECTCOLUMNS( CALCULATETABLE( FILTER( 'Action_Occurrences', DATEDIFF('Action_Occurrences'[Date], UTCTODAY(), DAY) <= 10 ), 'Action_Types'[Action Description] = "Find a treasure" ), "Party", RELATED('Party_Actions'[Party ID]) ) // Use NOT to remove the players that are in a party that does not fill the conditions RETURN FILTER('Players', NOT('Players'[Party ID] in _Unwanted)) ), // Players that are wizards and that have a party Player_Families[Family] = "Wizard", KEEPFILTERS(NOT(ISBLANK('Players'[Party ID]))) )