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

[leste]

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?

[leste]

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])))
)

[ValtteriN · Accepted solution]

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/

[leste]

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 🧙