Power BI Direct Query下DAX计算列及关联键视觉对象报错求助

项目背景 #

我正在开展提升Power BI技能的项目，已在Oracle数据库创建三个表（data_2018、market segment、meals），并通过以下SQL语句以Direct Query模式导入数据：

select * from data_2018 
left join  meal on data_2018.meal = meal.meal 
left join client_segment 
on data_2018.market_segment = client_segment.market_segment

随后用DAX创建计算列以计算酒店收入：

total_nights = Query1[STAYS_IN_WEEKEND_NIGHTS] + Query1[STAYS_IN_WEEK_NIGHTS]
room_no_discount = (Query1[DISCOUNT])* Query1[total_nights] *Query1[ADR]

遇到的问题 #

• 无法在视觉对象中显示room_no_discount列，出现对应错误；
• 使用关联键（Meals或Market segment）制作视觉对象时，始终出现对应错误。

排查与解决建议 #

针对room_no_discount列无法显示的问题 #

1. 检查源数据类型：确认DISCOUNT、STAYS_IN_WEEKEND_NIGHTS、STAYS_IN_WEEK_NIGHTS、ADR的字段类型是否为数值型。如果DISCOUNT是带百分号的文本，需在SQL中转换为数值，示例：

   SELECT 
       *,
       NVL(CAST(DISCOUNT AS NUMBER(5,2)), 0) AS DISCOUNT_CLEAN
   FROM data_2018
   LEFT JOIN meal ON data_2018.meal = meal.meal
   LEFT JOIN client_segment ON data_2018.market_segment = client_segment.market_segment
   

2. 处理空值：源数据中空值会导致计算列返回空值，视觉对象可能无法渲染。在SQL中用NVL函数将空值默认设为0，比如NVL(STAYS_IN_WEEKEND_NIGHTS, 0)。
3. 验证DAX语法：确认Query1是导入后的查询名称，字段名大小写与Power BI中显示的完全一致（Direct Query模式下大小写敏感）。

针对关联键视觉对象报错的问题 #

1. 修正SQL表名拼写：原表名为market segment（带空格），Oracle中需用双引号包裹，否则会解析错误。若client_segment是market segment的误写，应改为：

   select * from data_2018 
   left join meal on data_2018.meal = meal.meal 
   left join "market segment" 
   on data_2018.market_segment = "market segment".market_segment
   

2. 确认关联字段一致性：检查关联字段的类型、长度是否完全匹配（比如data_2018.meal和meal.meal是否同为VARCHAR2(30)），类型不匹配会导致关联数据异常。
3. 移计算逻辑到SQL：Direct Query对DAX计算列有较多限制，建议直接在SQL中计算total_nights和room_no_discount，减少Power BI端的计算压力，示例：

   SELECT 
       *,
       STAYS_IN_WEEKEND_NIGHTS + STAYS_IN_WEEK_NIGHTS AS total_nights,
       DISCOUNT * (STAYS_IN_WEEKEND_NIGHTS + STAYS_IN_WEEK_NIGHTS) * ADR AS room_no_discount
   FROM data_2018
   LEFT JOIN meal ON data_2018.meal = meal.meal
   LEFT JOIN "market segment" ON data_2018.market_segment = "market segment".market_segment
   

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