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 c...
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.

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]'". 

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Use your date column and table name instead of that

    • adriancho_BI's avatar
      adriancho_BI
      Regular Visitor
      2 years ago

      You're right, that was the first thing I did after copy your suggestion /: then the error showed up.

      Thanks anyways!