topic Re: Card trouble - need a DAX solution in DAX Commands and Tips

Card trouble - need a DAX solution

Anonymous — Wed, 09 Sep 2020 23:58:34 GMT

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!

Re: Card trouble - need a DAX solution

Greg_Deckler — Thu, 10 Sep 2020 00:48:42 GMT

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?

Re: Card trouble - need a DAX solution

amitchandak — Thu, 10 Sep 2020 02:02:29 GMT

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

Re: Card trouble - need a DAX solution

Anonymous — Thu, 10 Sep 2020 15:48:24 GMT

Yay! Thank you Greg! It worked on the second try.

Re: Card trouble - need a DAX solution

Anonymous — Thu, 10 Sep 2020 15:49:45 GMT

Unfortunately I could not get this to work. Thank you for the reply though.