Forum Discussion
Optimizing Dax formula
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 )
)
Can anyone tell me why is this formula not working on current version of power bi?
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 )
11 Replies
Hi Anonymous ,
Please make sure your table names are consistent, otherwise errors will occur. Change table name to either 'Date' or Date.
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 )
PaulDBrown It's working Thanks
what error message are you getting?
www.daxformatter.com isn't throwing any errors.
are you creating a calculated measure or calculated column? also have you run this successfully before?
