Forum Discussion
Card trouble - need a DAX solution
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!
Anonymous -
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?
4 Replies
- Greg_Deckler
Community Champion
Anonymous -
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?
- amitchandak
Super User
Anonymous , 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] ))
