Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Need help with a DAX formula. I am trying to add a filter to a card to achieve "Blank" cards when the condition is not met. The condition is that 5 players or more must play in order for the card to show the data.
Weighted average score is a measure of how all players did in a particular sport on a particular day (governed by slicers on the page) -
WeightedAvgScore = SUMX([PlayerScore] * [Weight]) / SUMX([Weight]) |
# of players depends on the sport and the day-
I created the #ofplayers flag as a measure to see if 1's and 0's could help with filtering. It did not.
Team | Sport | WeightedAvgScore | # of players | #ofplayersMeasure (flag) |
School A | Soccer | 20 | 10 | 1 |
School A | Tennis | 34 | 2 | 0 |
School A | Baseball | 63 | 12 | 1 |
School B | Soccer | 123 | 10 | 1 |
School B | Football | 23 | 3 | 0 |
School B | Baseball | 26 | 12 | 1 |
School C | Football | 36 | 10 | 1 |
School C | Tennis | 32 | 2 | 0 |
School C | Baseball | 23 | 12 | 1 |
Desired result on card is the last column:
Result | Display when # of players > 5 | |
Team | Sport | WeightedAvgScore CARD |
School A | Soccer | 20 |
School A | Tennis | Blank |
School A | Baseball | 63 |
School B | Soccer | 123 |
School B | Football | Blank |
School B | Baseball | 26 |
School C | Football | 36 |
School C | Tennis | Blank |
School C | Baseball | 23 |
This forumla did not filter properly -
=SUMX(FILTER(Table1
CALCULATE(DISTINCTCOUNT([PlayerID),[PlayerID]>15)),
SUMX([PlayerScore] * [Weight]) / SUMX([Weight])
Please help!
Solved! Go to Solution.
@chandi -
5 or more measure =
VAR __Team = MAX([Team])
VAR __Sport = MAX([Sport])
VAR __5orMore = IF([# of players] >=5,1,0)
RETURN
IF(__5orMore,[WeightedAverage],BLANK())
Assumes a WeightedAverage measure. Do you need help with that as well?
@chandi , Try like
Measure =
var _dist =CALCULATE(DISTINCTCOUNT([PlayerID]))
return
SUMX(FILTER(Summarize(Table1,Table1[Team] ,Table1[Sport],"_1",_dist, "_2",SUMX(Table,[PlayerScore] * [Weight]),"_3",SUMX([Weight])),[_1]_dist>15),divide([_2] /[_3] ))
Unfortunately I could not get this to work. Thank you for the reply though.
@chandi -
5 or more measure =
VAR __Team = MAX([Team])
VAR __Sport = MAX([Sport])
VAR __5orMore = IF([# of players] >=5,1,0)
RETURN
IF(__5orMore,[WeightedAverage],BLANK())
Assumes a WeightedAverage measure. Do you need help with that as well?
Yay! Thank you Greg! It worked on the second try.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |