从Databricks目录的DateTime列提取星期几的DAX报错问题咨询

使用Databricks目录数据，日期时间列格式为14/03/2001 1:30:55 PM (General Date)，尝试提取星期几（周一至周日）时，以下DAX表达式均报错：

尝试1：自定义计算星期几 #

DayName = SWITCH(TRUE(),
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 0, "Sunday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 1, "Monday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 2, "Tuesday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 3, "Wednesday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 4, "Thursday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 5, "Friday",
                 MOD(YEAR([YourDateTimeColumn]) * 10000 + MONTH([YourDateTimeColumn]) * 100 + DAY([YourDateTimeColumn]), 7) = 6, "Saturday")

错误：couldn't fold the expression into the data source. Please try a simpler expression.

尝试2：FORMAT函数匹配星期名 #

DayName = SWITCH(TRUE(),
                 FORMAT([YourDateTimeColumn], "dddd") = "Monday", "Monday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Tuesday", "Tuesday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Wednesday", "Wednesday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Thursday", "Thursday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Friday", "Friday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Saturday", "Saturday",
                 FORMAT([YourDateTimeColumn], "dddd") = "Sunday", "Sunday")

错误：Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models.

尝试3：WEEKDAY函数配合SWITCH #

DayNumber = WEEKDAY([YourDateTimeColumn], 2)
DayName = SWITCH([DayNumber], 
                 1, "Monday", 
                 2, "Tuesday", 
                 3, "Wednesday", 
                 4, "Thursday", 
                 5, "Friday", 
                 6, "Saturday", 
                 7, "Sunday")

错误：function weekday is not allowed as part of calculated column DAX expressions.

尝试4：直接用FORMAT提取星期名 #

DayOfWeek = FORMAT([YourDateTimeColumn], "dddd")

错误：Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models.

方案1：在Databricks端预处理数据（推荐） #

DirectQuery模式下多数DAX函数受限，最稳妥的方式是在Databricks中提前计算星期几，再同步到Power BI：

Spark SQL 代码示例 #

如果要直接修改表结构：

ALTER TABLE your_table_name
ADD COLUMN day_of_week STRING;

UPDATE your_table_name
SET day_of_week = date_format(to_timestamp(your_datetime_column, 'dd/MM/yyyy h:mm:ss a'), 'EEEE');

如果不想修改原表，可创建视图：

CREATE OR REPLACE VIEW your_view_name AS
SELECT *,
       date_format(to_timestamp(your_datetime_column, 'dd/MM/yyyy h:mm:ss a'), 'EEEE') AS day_of_week
FROM your_table_name;

• 若你的日期列已经是Databricks内置的日期/时间类型，可省略to_timestamp函数，直接用date_format(your_datetime_column, 'EEEE')
• 'EEEE' 返回完整星期名（如Monday、Sunday），若需要缩写可替换为'EEE'

方案2：DAX中使用支持的函数（需注意地域设置） #

在DirectQuery模式下，可使用DATEPART函数实现需求，但返回的星期序号依赖Power BI的地域设置：

DayName = 
VAR DayNum = DATEPART("weekday", [YourDateTimeColumn])
RETURN
SWITCH(DayNum,
       1, "Sunday",
       2, "Monday",
       3, "Tuesday",
       4, "Wednesday",
       5, "Thursday",
       6, "Friday",
       7, "Saturday")

若你的环境设置周一为一周第一天，需调整SWITCH中序号与星期名的对应关系。

方案3：使用计算表替代计算列 #

若无法修改Databricks数据，可创建计算表将数据导入Power BI后再计算星期几，适合数据量不大的场景：

CalculatedTable = 
SELECTCOLUMNS(
    your_table_name,
    "*",
    "DayName", 
    SWITCH(
        DATEPART("weekday", [YourDateTimeColumn]),
        1, "Sunday",
        2, "Monday",
        3, "Tuesday",
        4, "Wednesday",
        5, "Thursday",
        6, "Friday",
        7, "Saturday"
    )
)

内容的提问来源于stack exchange，提问作者Shanti