将 Case 语句、过滤器从 t-SQL 查询转换为 DAX [英] Converting Case statement, Filter from t-SQL query to DAX
问题描述
我在将下面的 t-sql 查询转换为 DAX 时遇到问题.
I have a problem converting below t-sql query into DAX.
概述 - 有两个示例表 - Table1 和 Table2 具有以下架构
Overview - There are two sample tables - Table1 and Table2 with below schema
表 1(ID varchar(20),名称 varchar(30))
Table1 (ID varchar(20),Name varchar(30))
表 2 (CapID varchar(20),CAPName varchar(30), CapID_Final varchar(20))
Table2 (CapID varchar(20),CAPName varchar(30), CapID_Final varchar(20))
请注意:以上表格之间存在一对多关系:Table2中的[ID]与Table1中的[CapID]
Please note : There exists one to many relationship between above tables : [ID] in Table2 with [CapID] in Table1
我正在尝试根据下面我的 t-SQL 查询中的条件导出 table2 中的 CapID_Final 列,它工作得非常好 -
I am trying to derive CapID_Final column in table2 based on conditions as per my t-SQL query in below which works perfectly fine -
SELECT CASE
WHEN [CapID] like 'CA%' and [CAPName]='x12345-Sample'
and [CapID] not in(select [ID] from Table1 where Name='x12345-Sample')
THEN 'Undefined_Cap_1'
WHEN [CapID] like 'CA%' and [CAPName]='z12345-Sample'
and [CapID] not in(select [ID] from Table1 where Name='z12345-Sample')
THEN 'Undefined_Cap_2'
WHEN [CapID] like 'CA%' and [CAPName]='a123-Sample'
and [CapID] not in(select [ID] from Table1 where Name='a123-Sample')
THEN 'Undefined'
ELSE [CapID]
END AS [CapID_Final] from Table2
但是,我希望在使用 DAX 的计算列中对 Power BI 中的 CapID_Final 列进行相同的推导.
However, I want the same derivation for CapID_Final column in Power BI in a calculated column using DAX.
到目前为止,我已经尝试了下面的代码 - 但即使匹配条件它也会返回未定义" -
So far, I have tried below code - but it returns "Undefined" for even matched conditions -
CapID_Final =
IF(LEFT(Table2[CapID],2)="CA" && Table2[CAPName]="z12345-Sample" &&
NOT
(COUNTROWS (
FILTER (
Table1,CONTAINS(Table1,Table1[ID],Table2[CapID])
)
) > 0),"Undefined_Cap_1","Undefined"
)
我不熟悉 DAX,但我尝试过但无法弄清楚.请告诉我如何在 Power BI 中将我的 sql 查询转换为等效的 DAX?
I am not familiar with DAX, however I tried and couldn't figure it out. Could you please let me know how to convert my sql query to equivalent DAX in Power BI?
推荐答案
这里的SWITCH
基本上相当于CASE
子句:
A SWITCH
is basically the equivalent of a CASE
clause here:
CapID_Final =
SWITCH (
TRUE (),
LEFT ( Table2[CapID], 2 ) = "CA"
&& Table2[CAPName] = "x12345-Sample"
&& NOT (
Table2[CapID]
IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "x12345-Sample" )
), "Undefined_Cap_1",
LEFT ( Table2[CapID], 2 ) = "CA"
&& Table2[CAPName] = "z12345-Sample"
&& NOT (
Table2[CapID]
IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "z12345-Sample" )
), "Undefined_Cap_2",
LEFT ( Table2[CapID], 2 ) = "CA"
&& Table2[CAPName] = "a12345-Sample"
&& NOT (
Table2[CapID]
IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "a12345-Sample" )
), "Undefined",
Table1[CapID]
)
您甚至可以对其进行一些重构以提高代码效率.假设我没有犯任何逻辑错误:
You might even be able to refactor it a bit to be more code efficient. Assuming I didn't make any logic mistakes:
CapID_Final =
VAR IDs =
CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = Table2[CAPName] )
RETURN
IF (
LEFT ( Table2[CapID], 2 ) = "CA"
&& NOT ( Table2[CapID] IN IDs ),
SWITCH (
Table2[CAPName],
"x12345-Sample", "Undefined_Cap_1",
"z12345-Sample", "Undefined_Cap_2",
"a12345-Sample", "Undefined"
),
Table1[CapID]
)
这篇关于将 Case 语句、过滤器从 t-SQL 查询转换为 DAX的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!