Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Untangle this crazy condtional DAX statement

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

 

 

 

 

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

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

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

     

    Thanks,

      • Anonymous's avatar
        Anonymous
        Not applicable

        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

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage

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