Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Optimizing Dax formula

Workday Number = VAR CurrentMonth = SELECTEDVALUE ( 'Date'[Year Month] ) VAR MonthTable = FILTER ( ALL ( 'Date' ), 'Date'[Working Days] = "Weekday" && 'Date'[Year Month] = CurrentMonth ) RET...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

     

    I would venture it is because your RANKX function doesn't have the column reference to rank the values by. Include a column reference after the table reference:

    RANKX ( MonthTable, MonthTable[...], CALCULATE ( AVERAGE ( Date[DayOfMonth] ) ),, ASC )

     

    See if that works.

     

    EDIT: you might also need an ALL or ALLSELECTED before the table expression, depending on the context you wish to calculate de Rank by:

    RANKX ( ALLSELECTED(MonthTable), MonthTable[...], CALCULATE ( AVERAGE ( Date[DayOfMonth] ) ),, ASC )

Anonymous's avatar
Anonymous
Not applicable
6 years ago

It says the syntax is incorrect but everything looks fine

 
 



vanessafvg's avatar
vanessafvg
Community Champion
6 years ago

www.daxformatter.com isn't throwing any errors.

 

are you creating a calculated measure or calculated column?  also have you run this successfully before?  

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I'm creating a calculated column, yaa it was running before but now it's not

    This is the error i get

     

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      6 years ago

      eish 🙂

       

      i can't see the fulll error, and you say the only difference is you have updated your power bi to the latest thats the only change?  strange that it doesn't actually underline the issue.   

    • Gordonlilj's avatar
      Gordonlilj
      Solution Sage
      6 years ago

      Hi,

       

      No idea if it would solve it but you could try adding a ' before and after the table name and to remove the calculate something like below

      RETURN
IF(SELECTEDVALUE ( 'Date'[Working Days] ) <> "Weekday",

BLANK (),
RANKX ( MonthTable, AVERAGE ( 'Date'[DayOfMonth]  ),, ASC )
)

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I don't think it will work,