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

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 )
)