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 )

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, 

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        6 years ago

        Anonymous  thats a different error now though which is strange.  i see you changed the field names or is that the real names ?

         

        if you go back to the original (although i can't see how this will work but i guess you have to eliminate all possiblities), and just add apostrophes so its named consistenly without does that make a difference?

         

        Workday Number =
        VAR CurrentMonth =
        SELECTEDVALUE ( 'Date'[Year Month] )
        VAR MonthTable =
        FILTER (
        ALL ( 'Date' ),
        'Date'[Working Days] = "Weekday"
        && 'Date'[Year Month] = CurrentMonth
        )
        RETURN
        IF (
        SELECTEDVALUE ('Date'[Working Days] ) <> "Weekday",
        BLANK (),
        RANKX ( MonthTable, CALCULATE ( AVERAGE ( 'Date'[DayOfMonth] ) ),, ASC )
        )