Optimizing Dax formula

[Anonymous]

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?

[PaulDBrown · Accepted solution]

@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]

@PaulDBrown  It's working Thanks

[v-eachen-msft]

Hi @Anonymous ,

Please make sure your table names are consistent, otherwise errors will occur. Change table name to either 'Date' or Date.

[vanessafvg]

what error message are you getting?

[Anonymous]

It says the syntax is incorrect but everything looks fine

[vanessafvg]

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]

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

This is the error i get

[Gordonlilj]

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]

I don't think it will work, 

[vanessafvg]

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

[vanessafvg]

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.