topic Re: Improvement of IF-CALCULATE calculation in DAX Commands and Tips

Improvement of IF-CALCULATE calculation

bmpi — Thu, 09 Jun 2022 17:39:19 GMT

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")) ,

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

Re: Improvement of IF-CALCULATE calculation

tamerj1 — Thu, 09 Jun 2022 18:01:32 GMT

hi bmpi

please try

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

Re: Improvement of IF-CALCULATE calculation

Syk — Thu, 09 Jun 2022 18:01:39 GMT

try something like this

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

Re: Improvement of IF-CALCULATE calculation

bmpi — Thu, 09 Jun 2022 18:17:53 GMT

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

Re: Improvement of IF-CALCULATE calculation

bmpi — Thu, 09 Jun 2022 18:18:35 GMT

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

Re: Improvement of IF-CALCULATE calculation

Syk — Thu, 09 Jun 2022 18:35:16 GMT

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!