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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
leste
Frequent Visitor

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

Hi!

 

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

 

leste_1-1676567597519.png

 

 

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?

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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:

ValtteriN_0-1676580576711.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
leste
Frequent Visitor

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
Super User
Super User

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:

ValtteriN_0-1676580576711.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 🧙

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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