Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

VAR formula error

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_...
Need Help
  • sdjensen's avatar
    sdjensen
    8 years ago

    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")
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    sdjensen
    Solution Sage
    8 years ago

    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's avatar
      sdjensen
      Solution Sage
      8 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        thanks sdjensen for the tip :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Barnee

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.