topic Re: New Measure with conditional counts in DAX Commands and Tips

New Measure with conditional counts

pickslides — Thu, 20 Oct 2022 10:03:10 GMT

Hi,

I want to create a measure that counts the number of '1's in one field [Rank] and divides it by the total number of cells that have a numerical value populated in [Score], in other words ignore 'UN' or blank cells. I will put this in a table or matrix as a group by

Score	Rank
32	2
33	3
45	1
21	5
24	3
UN	-
34	1
UN	-
37	2

Result in this examaple would give 2/7

Thanks, MQ

Re: New Measure with conditional counts

daXtreme — Thu, 20 Oct 2022 10:55:31 GMT

// Let your table be T. [Measure] = var NumberOfOnes = COUNTROWS( FILTER( T, // T[Rank] must be a numerical field // where "-" in your table must be // a real BLANK. Do not mix numbers // with text, please. Thanks. T[Rank] = 1 ) ) var NumberOfNonblanks = COUNTROWS( FILTER( T, T[Score] <> "UN" ) ) var Output = DIVIDE( NumberOfOnes, NumberOfNonblanks ) return Output