Day Name in DirectQuery

[SzymonKl]

Could you kindly assist me in creating a measure to display the day name based on the 'ORDERS' table and the [ORDER_DATE] column? It's important to consider that we are working with DirectQuery, and thus, the FORMAT function cannot be used.

My approach not working : Day Name = FORMAT('ORDERS'[ORDER_DATE], "dddd") 

Thanks

[mlsx4]

Hi @SzymonKl 

Are you using a calendar table? Maybe you can compute the day name there and get it by using the relation?

[SzymonKl]

No , the [ORDER_DATE] column is in 'ORDERS' table and is a Date/Time format.

[johnyip]

@SzymonKl Not sure about this, but does the following work?

Day Name(MEASURE) = FORMAT(MAX('ORDERS'[ORDER_DATE]), "dddd") 

Or it is not working anyway because of DirectQuery?

[SzymonKl]

Thanks for solution unfortunetlly i getting the error :

[johnyip]

Sorry for the typo: it is MAX(

Day Name(MEASURE) = FORMAT(MAX('ORDERS'[ORDER_DATE]), "dddd") 

[SzymonKl]

Thanks 

I see the day name but unfortunately, instead of two records, I get much more. It seems that now the order has been assigned to each Wash Site. Wash Sites are stored in the 'WASH_SITES' table, column [NAME].

Without measure : 

with measure:

[johnyip]

Please provide some sample fake data that follows the data structure of your actual data, and the relationship between them for checking.

[SzymonKl · Accepted solution]

Hi Thanks for your support today 🙂 

I was able to find the right solution, instead measure I create a new column : 

Day Name = SWITCH(WEEKDAY('ORDERS'[ORDER_DATE]),

                1, "Sunday",

                2, "Monday",

                3, "Tuesday",

                4, "Wednesday",

                5, "Thursday",

                6, "Friday",

                7, "Saturday",

                BLANK())

[johnyip]

good to know.