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
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 a player is a wizard that belongs to a party, it should only be counted in these cases:
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?
Solved! Go to 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/
Proud to be a Super User!
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])))
)
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/
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 🧙
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |