unabe to use WEEKNUM or DATEPART in sql + direct query

[Anonymous]

I'm trying to get directly from the database:

DATE and weekday (monday, tuesday) or in number (1,2)

When I insert the DATEPART formula PBI gives me an error (incompatible query)

If I only insert the date and try to transform it in day of week, it throws another error abount incompatibility of the function in Direct Query.

I'm working on an online dashboard (that's why I use DirectQuery), any ideas of how can get this data?

The other solution is to have a script that copies the data to another database with all the transformations and then use direct query from there, but it seems messy.

Any idea?

Thank you!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

You can create the following calculated columns in your table. If the DAX don't return your expected result, please share sample data of your column and post your desired result here.

weeknumber = WEEKNUM(YourTable[Date])

weekday = WEEKDAY(YourTable[Date])

Regards,
Lydia