VAR formula error

[Anonymous]

Hi Experts

I am trying to write the following VAR formula but keep on getting an error, cannot see the wood for the trees.... HELP

LocationCount =
VAR Final_Total = IF(CALCULATE(COUNTA(Case_Data[Location])=BLANK(),(CALCULATE(COUNTA(Case_Data[Location])))
VAR Current_Category = CALCULATE(SELECTEDVALUE(Case_Data[Location]),
ALLEXCEPT(Case_Data, Case_Data[Location]))
Return

IF(Current_Category = BLANK(),FORMAT(Final_Total), FORMAT(Final_Total, "0"))

[Barnee]

hi,

I think you miss a closing parenthesis at the end of the first VAR 

Barna

[Anonymous]

Hi Barna

I worked that out and now have....

still erroring

LocationCounts =
VAR Final_Total = IF(CALCULATE(COUNTA(Case_Data[Location])=BLANK(),0,(CALCULATE(COUNTA(Case_Data[Location]))))
VAR Current_Category = CALCULATE(SELECTEDVALUE(Case_Data[Location]),
ALLEXCEPT(Case_Data, Case_Data[Location]))
RETURN

IF(Current_Category = BLANK(),FORMAT(Final_Total), FORMAT(Final_Total, "0"))

[Barnee]

Could you provide a small sample dataset with the columns and datatypes you are using with this calc? So that I can have a closer look on that. 

[sdjensen · Accepted solution]

There is several errors in your formula - parenthesis in wrong places, and your first FORMAT is missing a second parameter. I have cleaned the parenthesis, but you still need to change your FORMAT to what ever you are trying to do.

LocationCounts =
VAR Final_Total = 
IF(
	CALCULATE( COUNTA(Case_Data[Location]) ) = BLANK(),
	0,
	CALCULATE( COUNTA(Case_Data[Location]) )
)
VAR Current_Category = 
CALCULATE(
	SELECTEDVALUE(Case_Data[Location]),
	ALLEXCEPT(Case_Data, Case_Data[Location])
)
RETURN

IF(
	Current_Category = BLANK(),
	FORMAT(Final_Total), 
	FORMAT(Final_Total, "0")
)

[sdjensen]

You should try to use daxformatter when writing your DAX - formatting your DAX correctly will help you spot errors and also make your DAX much easier to read/understand when you have to look at it months later.

[Barnee]

thanks @sdjensen for the tip 🙂

[sdjensen]

@Barnee - the tip was ment for @Anonymous. You seem to understand the DAX 🙂

[Anonymous]

All (experts)

many thanks for the excellent feedback and support....

[Anonymous]

Hi Barnee

thats i s going to be tricky cannot upload from ,my location... appricated your assistance so far....sorry

[Barnee]

so, without knowing what is your goal with this calculation I figured out some syntax mistakes.

LocationCounts = 
VAR Final_Total = IF(CALCULATE(COUNTA(Case_Data[Location]))=BLANK(),0,(CALCULATE(COUNTA(Case_Data[Location]))))
VAR Current_Category = CALCULATE(SELECTEDVALUE(Case_Data[Location]),
ALLEXCEPT(Case_Data, Case_Data[Location]))
RETURN

IF(Current_Category = BLANK(),Final_Total, FORMAT(Final_Total, "0"))

1. ) at the firts VAR you use a calculate function but as far as I see it has no second argument so you may want to close that CALCULATE before the =BLANK() part.

2) after the RETURN at the second argument of the IF function, you used a FORMAT function like this: FORMAT(Final_Total).
I'm not sure exactly what was your goal with it but the FORMAT function has to have two arguments like you used at the end of the calculation FORMAT(Final_Total, "0") . So that as i deleted the FORMAT function from the second argument and used the Final_Total only it worked for me.

Without context it was a bit hard to know what yo wanted but the code runs.