时间戳

首页 > 解决方案 > 基于 if 语句的 Power Bi-DAX Measure-Index Match

if-statement - 基于 if 语句的 Power Bi-DAX Measure-Index Match

问题描述

我有两个表两个表，表 1 名称称为“数据”，表 2 名称称为“报告”。

表 2（报告）标题名称分别称为“项目”、“水果列表”、“区域代码”、“语言代码”、“销售代码”、 “连接”和“状态”。有时在表 2 中根据标题有空白列。 在此处输入图像描述

表 1（数据）标题名称称为“水果列表”、“区域代码”、“语言代码”、“销售代码”和“连接” 在此处输入图像描述 我为两个表创建了“连接”列以合并查询（表 2 至表 1)。我不直接合并表的原因是因为它会在 Table2(Report) 中创建很多重复列。

在基于“连接”列合并表后，我创建了状态列的条件 - “是”或“否”。

在此处输入图像描述

现在我想在这里实现什么，基于状态列我想根据标题“水果列表1”、“区号1”、“语言代码1”、“销售代码1”填充其他状态列-“错误”或“无错误”在表 2（报告）中。

如果状态列是“是”，则根据表 2（报告）中的标题“水果列表 1”、“区域代码 1”、“语言代码 1”、“销售代码 1”返回输出“无错误”。列 H：K my输出列。

如果状态栏为“否”，则根据表 2（报告）中的标题“水果列表 1”、“区域代码 1”、“语言代码 1”、“销售代码 1”返回输出“错误”。H 列：K 我的输出列。

在此处输入图像描述

附在此处的文件供您参考。

https://www.dropbox.com/s/rbb71gsjqq39uv8/INDEX%20MATCH%20MULTIPLE%20RESULT.pbix?dl=0 https://www.dropbox.com/s/m1onkdmlmfryjmx/INDEX%20MATCH%28OK%2CYES%29 .xlsx?dl=0

标签： if-statementpowerbimulti-indexpowerbi-desktop

解决方案

您需要在Power Query 编辑器中进行一些转换才能应用以实现所需的输出。下面是高级编辑器的代码-

let
    Source = Excel.Workbook(File.Contents("C:\Users\Khademur Rabbani\Downloads\INDEX MATCH(OK,YES).xlsx"), null, true),
    REPORT_Sheet = Source{[Item="REPORT",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(REPORT_Sheet, [PromoteAllScalars=true]),
    #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Blank Rows",{"REPORT.CONCATENATE", "DATA.CONCATENATE", "STATUS", "FRUIT LIST_1", "AREA CODE_2", "LANGUAGE_3", "SALES CODE_4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"FRUIT LIST", type text}, {"AREA CODE", type text}, {"LANGUAGE", type text}, {"SALES CODE", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "FRUIT LIST", "AREA CODE", "LANGUAGE", "SALES CODE"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"FRUIT LIST"}, #"DATA (2)", {"FRUIT LIST"}, "DATA (2)", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [#"DATA (2)"][FRUIT LIST]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Max([Custom])),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"FRUIT LIST", "AREA CODE"}, #"DATA (2)", {"FRUIT LIST", "AREA CODE"}, "DATA (2).1", JoinKind.LeftOuter),
    #"Added Custom2" = Table.AddColumn(#"Merged Queries1", "Custom.2", each [#"DATA (2).1"][AREA CODE]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Max([Custom.2])),
    #"Merged Queries2" = Table.NestedJoin(#"Added Custom3", {"FRUIT LIST", "LANGUAGE"}, #"DATA (2)", {"FRUIT LIST", "LANGUAGE"}, "DATA (2).2", JoinKind.LeftOuter),
    #"Added Custom4" = Table.AddColumn(#"Merged Queries2", "Custom.4", each [#"DATA (2).2"][LANGUAGE]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each List.Max([Custom.4])),
    #"Merged Queries3" = Table.NestedJoin(#"Added Custom5", {"FRUIT LIST", "SALES CODE"}, #"DATA (2)", {"FRUIT LIST", "SALES CODE"}, "DATA (2).3", JoinKind.LeftOuter),
    #"Added Custom6" = Table.AddColumn(#"Merged Queries3", "Custom.6", each [#"DATA (2).3"][SALES CODE]),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom.7", each List.Max([Custom.6])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom7",{"DATA (2)", "Custom", "DATA (2).1", "Custom.2", "DATA (2).2", "Custom.4", "DATA (2).3", "Custom.6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "FRUIT LIST 1"}, {"Custom.3", "AREA CODE 2"}, {"Custom.5", "LANGUAGE 3"}, {"Custom.7", "SALES CODE 4"}}),
    #"Added Custom8" = Table.AddColumn(#"Renamed Columns", "STATUS", each if 
    [FRUIT LIST 1] <> null 
    and [AREA CODE 2] <> null 
    and [LANGUAGE 3] <> null 
    and [SALES CODE 4] <> null then "NO ERROR" 
else "ERROR"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom8",{"Index", "FRUIT LIST", "AREA CODE", "LANGUAGE", "SALES CODE", "STATUS", "FRUIT LIST 1", "AREA CODE 2", "LANGUAGE 3", "SALES CODE 4"}),
    #"Added Custom9" = Table.AddColumn(#"Reordered Columns1", "FRUIT_LIST_1", each if [FRUIT LIST 1] = null then "ERROR" else "NO ERROR"),
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "AREA_CODE_2", each if [AREA CODE 2] = null then "ERROR" else "NO ERROR"),
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "LANGUAGE_3", each if [LANGUAGE 3] = null then "ERROR" else "NO ERROR"),
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "SALES_CODE_4", each if [SALES CODE 4] = null then "ERROR" else "NO ERROR"),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom12",{"FRUIT LIST 1", "AREA CODE 2", "LANGUAGE 3", "SALES CODE 4"})
in
    #"Removed Columns2"

这是输出 -

在此处输入图像描述

由于代码/步骤很大，请从此链接找到报告文件。请检查表REPORT (2)和DATA (2)的转换

推荐阅读