请求将指定Databricks SQL查询通过Lookup转换为DAX语句

以下是对应你提供的SQL查询、使用LOOKUPVALUE函数实现的DAX代码：

DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            gold_userclinicbridge,
            LOOKUPVALUE(gold_dimuser[InternalEmailAddress], gold_dimuser[UserId], gold_userclinicbridge[UserId]) = @UserId
        ),
        "ProgramId", 
        LOOKUPVALUE(
            gold_programid[ProgramId], 
            gold_programid[ProgramId], 
            LOOKUPVALUE(gold_dimclinic[ProgramId], gold_dimclinic[ClinicId], gold_userclinicbridge[ClinicId])
        ),
        "ProgramName", 
        LOOKUPVALUE(
            gold_programid[ProgramName], 
            gold_programid[ProgramId], 
            LOOKUPVALUE(gold_dimclinic[ProgramId], gold_dimclinic[ClinicId], gold_userclinicbridge[ClinicId])
        ),
        "InternalEmailAddress", 
        LOOKUPVALUE(gold_dimuser[InternalEmailAddress], gold_dimuser[UserId], gold_userclinicbridge[UserId])
    )
)

逻辑对应说明 #

• 过滤逻辑：用FILTER配合LOOKUPVALUE实现原SQL的WHERE DU.InternalEmailAddress = @UserId，先从gold_userclinicbridge中筛选出关联用户匹配指定邮箱的记录
• 关联取值：嵌套使用LOOKUPVALUE模拟SQL的INNER JOIN：
  • 先通过gold_userclinicbridge[ClinicId]从gold_dimclinic取ProgramId
  • 再用该ProgramId从gold_programid取对应的ProgramId和ProgramName
  • 通过gold_userclinicbridge[UserId]从gold_dimuser取InternalEmailAddress
• 去重与字段选择：SELECTCOLUMNS对应SQL的SELECT列表，DISTINCT实现去重效果

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