Power BI - SQL query with Case When to DAX?

Question

我正试图将下面的SQL查询转换为我的Power BI报告上的DAX。

SQL QUERY: -

select  Date
,Id
,MAX(CASE WHEN fkid = 1 then 1 else 0 end) as rflag
,MAX(CASE WHEN fkid = 128 then 1 else 0 end) as dflag
,CASE WHEN MAX(CASE WHEN fkid = 1 then 1 else 0 end) = 1 AND MAX(CASE WHEN fkid = 128 then 1 else 0 end)=1 THEN 1 ELSE 0 END AS BOTH
from table
GROUP BY Date,Id

输入表:-

DATE        ID  FKID
09-07-2021  1   1
09-07-2021  1   128
09-07-2021  2   1
09-07-2021  3   128
10-07-2021  4   1
10-07-2021  4   128
10-07-2021  1   1
10-07-2021  1   128

最终输出:-

DATE        FKID    RFLAG   DFLAG   BOTH
09-07-2021  1       1       1       1
09-07-2021  2       1       0       0
09-07-2021  3       0       1       0
10-07-2021  1       1       1       1
10-07-2021  4       1       1       1

我已经尝试实现以下dax查询。

DAX指数:-

TEMTABLE = 
GROUPBY(
TABLE,
TABLE[DATE],
TABLE[FKID],
"rflag", MAXX(CURRENTGROUP(),CALCULATE(TABLE,TABLE[fkID]=1),
"dflag", MAXX(CURRENTGROUP(),CALCULATE(TABLE,TABLE[fkID]=128),
"both",  MAXX(MAXX([rflag])=1 AND MAXX([dflag])=1)
)

Answer 1

PowerQuery解决方案:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNTDXNTIwMlTSUYLgWB1swkYWmBJG2NUbI6k3NECSMIGrxxTGpt4Qu3q4e2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, ID = _t, FKID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ID", Int64.Type}, {"FKID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "rflag", each if [FKID] = 1 then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "dflag", each if [FKID] = 128 then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"DATE", "ID"}, {{"rflag", each List.Max([rflag]), type number}, {"dflag", each List.Max([dflag]), type number}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "both", each if ( [rflag] = 1 and [dflag] = 1 ) then 1 else 0)
in
#"Added Custom2"

综合解决方案:

Table 2 = 
ADDCOLUMNS ( 
SUMMARIZE (
'Table' , 
'Table'[DATE], 'Table'[ID],
"rflag", IF ( CONTAINS ( 'Table', 'Table'[FKID] , 1 ) , 1 , 0 ),
"dflag", IF ( CONTAINS ( 'Table', 'Table'[FKID] , 128 ) , 1 , 0 )
),
"both", IF ( [dflag] = 1 && [rflag] = 1 , 1 , 0 )
)