Forum Discussion

adriancho_BI's avatar
adriancho_BI
Regular Visitor
2 years ago
Solved

How to get day name on Direct Query

Hello! 
I'm trying to get the day name (monday, sunday, etc.) and I'm using DirectQuery, this limit the formulas I can use. I mean that I can't use WEEKDAY OR WEEKNUM directly. And I would like to create a new column that tells me this field. My date column is datetime.

I already have different "Start of" columns, like: Start of Day, of hour, of Month, of Week

Thank you for the help.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi adriancho_BI ,

    bhanu_gautam Thanks for your concern about this case! I think your answer is pretty close.
    After my testing, in fact you only need to use this DAX to create a measure, but it can't be achieved as a column:

     

    Measure = FORMAT(MAX('test'[Resolved Date]), "dddd")

     

    The final output is as below:

    The test environment I use is Direct Query connecting to SQL.

    And if you just want to know what day of the week the date is, you can directly select the Format here:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    adriancho_BI , You can try using format and left function 

     

    Day Name =
    SWITCH(
    TRUE(),
    LEFT(FORMAT([Date], "dddd"), 3) = "Mon", "Monday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Tue", "Tuesday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Wed", "Wednesday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Thu", "Thursday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Fri", "Friday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Sat", "Saturday",
    LEFT(FORMAT([Date], "dddd"), 3) = "Sun", "Sunday",
    ""
    )

    • adriancho_BI's avatar
      adriancho_BI
      Regular Visitor
      2 years ago

      thank you for the alternative, but I can't use directly the date column aparently because PBI shows me the error "Can't find name '[date]'". 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi adriancho_BI ,

    bhanu_gautam Thanks for your concern about this case! I think your answer is pretty close.
    After my testing, in fact you only need to use this DAX to create a measure, but it can't be achieved as a column:

     

    Measure = FORMAT(MAX('test'[Resolved Date]), "dddd")

     

    The final output is as below:

    The test environment I use is Direct Query connecting to SQL.

    And if you just want to know what day of the week the date is, you can directly select the Format here:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi adriancho_BI ,

      And if you want to achieve this in a column, maybe you can try to use Power Query:
      Here is my sample data:

      Use this M function to create a custom column:

      Date.DayOfWeek([Resolved Date], Day.Monday) + 1

      Then create a conditional column:

      And the final output is as below:

      Then click "Close & Apply".

      Here is the M function of these two steps:

      #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Date.DayOfWeek([Resolved Date], Day.Monday) + 1),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 1 then "Monday" else if [Custom] = 2 then "Tuesday" else if [Custom] = 3 then "Wednesday" else if [Custom] = 4 then "Thursday" else if [Custom] = 5 then "Friday" else if [Custom] = 6 then "Saturday" else if [Custom] = 7 then "Sunday" else null)



      Best Regards,
      Dino Tao
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.