Forum Discussion

DQuigg's avatar
DQuigg
Icon for Advocate III rankAdvocate III
7 years ago

LastDate Filter Iterates to Undesired Result

I am running across a specific way that my DAX expression is interpreted.  The DAX is below.

 

Fall New Hires =CALCULATE (SUMX ('STFF_Fall New Hires by Gender and Race (S_NH)','STFF_Fall New Hires by Gender and Race (S_NH)'[Value]),'STFF_Fall New Hires by Gender and Race (S_NH)_Dimensions'[Series Group] = 1,'Fall New Hires Series Bridge'[Gender] <> 0,'Fall New Hires Series Bridge'[Race] <> 0,'STFF_Fall New Hires by Gender and Race (S_NH)_Dimensions'[Occupation] <> 0,LASTDATE ( 'STFF_Fall New Hires by Gender and Race (S_NH)'[Date] ))

 

The intent of the Last Date is to default the calculation always to the last date in the FACT table for each applied dimensions. The problem is the Last Date is applied independently to each dimension. So for example, the result table below shows New Hires by occupation and two of the hires (Healthcare and Sales Support) were not in 2017, but in 2014. The correct table would show if you put the years in the columns (second image).  I need to modify the expression somehow so that the last date will be applied across all the dimensions uniformly and not per individual dimension.  Any ideas!

Incorrect

 

Correct

 

 

 

Tips and Tricks

4 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    DQuigg ,

    So, you really want the last date to be equal to 2017 to show in the table? Or if the hire for that occupation is not zero?

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    7 years ago

    Not sure I got it completely. But in sumx, you should able to get a max of date, that will follow other slicer or when you add them to matrix/table 

     

    USE max = CALCULATE(AVERAGEx(Sales,max(Sales[Order_Date])-Sales[Order_Date]))

    This allowing have Avg date diff from the max for each date. 

     

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      7 years ago

      amitchandak , DQuigg ,

       

      I believe the issue is where a category shows on his Fall New Hires for 2017, when indeed there were no new hires. And looking more closely, what the table is picking up, is the total from the other table, which in itself, is the latest number of hires from any year.

       

      Nathaniel

      • DQuigg's avatar
        DQuigg
        Icon for Advocate III rankAdvocate III
        7 years ago

        Hi Nathaniel,

         

        I think you are correct.  I only want the new hires for any occupations in 2017 (or the last date in the fact table).  The problem is if there is an occupation that hired in a previous year but not the most recent year. It is picking up those occupations. Note my total is correct so it is messing up the table.

         

        So in essence, I think I evaluate the expression to get the last date where any occupation sum is greater than zero and then get the occupations for that date. I am pretty sure SUMX will work here but not sure how to write the syntax to make it work.  It is an inner expression within an outer expression.