Untangle this crazy condtional DAX statement

Anonymous — Tue, 27 Aug 2019 17:54:43 GMT

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

Re: Untangle this crazy condtional DAX statement

Anonymous — Tue, 27 Aug 2019 18:06:29 GMT

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

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

Thanks,

Re: Untangle this crazy condtional DAX statement

Anonymous — Tue, 27 Aug 2019 20:46:40 GMT

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-4f71-bed3-c8ae6d2b276e

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

Re: Untangle this crazy condtional DAX statement

kentyler — Tue, 27 Aug 2019 21:26:11 GMT

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

Re: Untangle this crazy condtional DAX statement

Anonymous — Wed, 28 Aug 2019 13:28:47 GMT

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

topic Re: Untangle this crazy condtional DAX statement in DAX Commands and Tips

Untangle this crazy condtional DAX statement

Re: Untangle this crazy condtional DAX statement

Re: Untangle this crazy condtional DAX statement

Re: Untangle this crazy condtional DAX statement

Re: Untangle this crazy condtional DAX statement