转换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)，Name 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))

请注意:上面的表之间存在一对多的关系:表2中的[ID]与表1中的[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并不熟悉，但是我尝试了却无法弄清楚.您能否让我知道如何将SQL查询转换为Power BI中的等效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屋！