Improvement of IF-CALCULATE calculation

[bmpi]

I have a formula that runs slow, which I am looking to improve..

Is there a more efficient way to write this formula?

(I want to return 0 if the colour is not "blue" for average calculation purposes)

In addition I want to return blank on all dates larger than today, but I did not find a way to make this work for this calculation.

# Blue People =

 IF(

    ISBLANK(CALCULATE([# People], Product[Colour] = "Blue")) ,

       0,

     CALCULATE([# People], Product[Colour] = "Blue"))

[Syk]

try something like this

Blue people =
var BluePeeps = CALCULATE([# People],Product[Colour]="Blue")
RETURN if(MAX('Date'[Date])<TODAY(), BluePeeps,0)

[bmpi]

Thanks!

Ended up with a mix of both suggestions:

# Blue People  =

VAR BluePeeps = CALCULATE([# People], Product[Colour] = "Blue"))

VAR COAL = COALESCE(BluePeeps,0)

RETURN IF(max(dimDate[Date]) <= TODAY(), COAL, BLANK())

[Syk]

Good to hear! For a bit simpler return you can leave off the last BLANK(). If you don't include anything for the false part of the if statement, it will automatically return blank!

[tamerj1 · Accepted solution]

hi @bmpi 

please try

=
COALESCE ( CALCULATE ( [# People], Product[Colour] = "Blue" ), 0 )

[bmpi]

Thanks, work like a charm.

Ended up with a mix of both suggestions:

# Blue People  =

VAR BluePeeps = CALCULATE([# People], Product[Colour] = "Blue"))

VAR COAL = COALESCE(BluePeeps,0)

RETURN IF(max(dimDate[Date]) <= TODAY(), COAL, BLANK())