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.
This a fine community that has taught me a lot. Thanks for your help on this one.
I have generally used short single purpose DAX statements and am what one might call a low-intermediate in DAX. I would love some help untangling this huge crazy DAX formula my predicessor left as some sort of conditional statement in an Excel cell.
In reverse engineering this I have come across some aspects of the formula syntax I haven't seen nor have come to a conclusion after 6 hours of internet searching.
I first thought this was a nested if statement, but don't see 'If" and the only DAX function I see is "AND". I can see a pattern of conditions but it doesn't look like nesting that I know of.
Here is the basic pattern:
=AND([@[Allocated Salary]]>0, [@[Revised Salary]]=0, [@[Change Effective Date]]=0, [@[End Date]]=0, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND . . .
It quasi repeats with different conditions.
The whole statement in the cell is pasted below.
Things that puzzle me:
1. What is the significance of the space between the ") [" that I highlighted in orange below?
2. Similiarly, I find that space to include a '0' sometimes (highlighed in blue below?
3. Is this even DAX?
What is going on in this huge statement?
=AND([@[Allocated Salary]]>0, [@[Revised Salary]]=0, [@[Change Effective Date]]=0, [@[End Date]]=0, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]>$AE$1, [@[End Date]]<=$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>0, [@[Change Effective Date]]=0, [@[End Date]]=0, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>0, [@[Change Effective Date]]>=$AE$1, [@[End Date]]=0, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Revised Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>0, [@[Change Effective Date]]<=$AE$1, [@[End Date]]=0, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Revised Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]=0, [@[End Date]]<=$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) 0 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]>=0, [@[End Date]]<=$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) 0 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]=0, [@[End Date]]>$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]<=$AE$1, [@[End Date]]>$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Revised Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2 AND([@[Allocated Salary]]>0, [@[Revised Salary]]>=0, [@[Change Effective Date]]>$AE$1, [@[End Date]]>$AE$1, 'Controls'!$D$4=0, 'Controls'!$E$4=0) [@[Allocated Salary]]
Thanks for your help in advance.
John Napier
Solved! Go to Solution.
Nope, this isnt DAX. This is an excel formula built using a table. Which is why you see [@[...]] structure
Good luck trying to figure out what that formula is doing.
I ran this through the Dax formatter at daxformatter.com and it doesn't format as DAX
Help when you know. Ask when you don't!
Oh, the file is a .xlsb (binary workbook).
Not sure why that file type is used except for performance aspects.
Thanks,
Nope, this isnt DAX. This is an excel formula built using a table. Which is why you see [@[...]] structure
Good luck trying to figure out what that formula is doing.
Thanks for looking into this, guys.
I have used basic structured references in Excel, but this method of conditional statement is new to me. What I figure is this is like a nested IF statement listing the conditions withing the AND() followed by a space and the THEN componant. I just haven't seen such syntax. It works though.
=AND([@[Allocated Salary]]>0,
[@[Revised Salary]]=0,
[@[Change Effective Date]]=0,
[@[End Date]]=0,
'Controls'!$D$4=0,
'Controls'!$E$4=0) [@[Allocated Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2
AND([@[Allocated Salary]]>0,
[@[Revised Salary]]>0,
[@[Change Effective Date]]>=$AE$1,
[@[End Date]]=0,
'Controls'!$D$4=0,
'Controls'!$E$4=0) [@[Revised Salary]]/'US Working Days'!$E$3*'US Working Days'!$B$2
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 |
---|---|
44 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |