Untangle this crazy condtional DAX statement

[Anonymous]

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

[kentyler]

I ran this through the Dax formatter at  daxformatter.com and it doesn't format as DAX

[Anonymous]

Oh, the file is a .xlsb (binary workbook).  

Not sure why that file type is used except for performance aspects.

Thanks,

[Anonymous · Accepted solution]

Nope, this isnt DAX. This is an excel formula built using a table.  Which is why you see [@[...]] structure

https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337...

Good luck trying to figure out what that formula is doing. 

[Anonymous]

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