Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"))
Solved! Go to 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") )
hi,
I think you miss a closing parenthesis at the end of the first VAR
Barna
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"))
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.
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") )
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 - the tip was ment for @Anonymous. You seem to understand the DAX 🙂
All (experts)
many thanks for the excellent feedback and support....
Hi Barnee
thats i s going to be tricky cannot upload from ,my location... appricated your assistance so far....sorry
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |