Forum Discussion

Redraidas1's avatar
Redraidas1
Icon for Helper I rankHelper I
5 years ago
Solved

Count NON-blank measures

 

Is there any way to count how many NON-Blank measures are in your variable? I want to use COUNTA but PowerBI won't let me. Is there any way to make it work? For example, if Calc1 returns 10, Calc 2 returns 15 and Calc 3 is blank, than I want my measure to return count 2. 

 

Measure 1 =

VAR Calculations =
[Calc1] + [Calc2] + [Calc3] 

RETURN
COUNTA(Calculations)  -- this bit gets squiggled in red
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Rather than using COUNTA, you could use an IF statement 

     

    Measure 1 =

    VAR _1 = IF([Calc1]>0, 1,0)
    VAR _2 = IF([Calc2]>0, 1,0)
    VAR _3 = IF([Calc3]>0, 1,0)

    Return 
    _1 + _2 + _3

    Essentially if Calc1 is more than 0 then give it a value of 1, same with calc2 and 3 then add them all up at the end.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Rather than using COUNTA, you could use an IF statement 

     

    Measure 1 =

    VAR _1 = IF([Calc1]>0, 1,0)
    VAR _2 = IF([Calc2]>0, 1,0)
    VAR _3 = IF([Calc3]>0, 1,0)

    Return 
    _1 + _2 + _3

    Essentially if Calc1 is more than 0 then give it a value of 1, same with calc2 and 3 then add them all up at the end.