Power Bi-DAX Measure-Index Match based on the if statement

1
votes

I have a two table two tables, Table 1 name called "Data" and Table 2 name called "Report".

Table 2(Report) header name called "item", "Fruit List", "Area Code", "Language Code", "Sales Code" "Concatenate" and "status". Sometimes in Table 2 has blanks columns according to the headers. enter image description here

Table 1(Data) header name called "Fruit List", "Area Code", "Language Code", "Sales Code" and "Concatenate" enter image description here I created "Concatenate" columns for both tables in order to merge query (tables 2 to table1). The reason I am not merge the table directly because it will created lot of duplication columns in Table2(Report).

After I merged the tables based on the "Concatenate" columns, I created if conditions for status columns-"Yes" or "No".

enter image description here

Now what I want achieve here, based on the status column I want populated additional status columns- "ERROR" or "NO ERROR" according to the headers "Fruit List1", "Area Code1", "Language Code1", "Sales Code1" in Table 2(Report).

If status columns is "Yes" then return the output "No Error" according to the headers "Fruit List1", "Area Code1", "Language Code1", "Sales Code1" in Table 2(Report).Column H:K my output columns.

If status columns is "No" then return the output "Error" according to the headers "Fruit List1", "Area Code1", "Language Code1", "Sales Code1" in Table 2(Report). Column H:K my output columns.

enter image description here

File attached here for your additional reference.

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

1
if-statementpowerbimulti-indexpowerbi-desktop
can you please advise anyone on this thread? – PETER
your logic for FRUIT_LIST_1, AREA_CODE_2..... is not that clear. Can you explain a bit more? – mkRabbani
okay. if status columns is yes, that means the data matched from table 1 to table2. if status is "no" something not matched from table to table 2. – PETER
If something not matched, FRUIT_LIST_1, AREA_CODE_2..... all 4 column will hold ERROR? or only part that do not matched? – mkRabbani
mismatched. dropbox.com/s/7lmvabv7wxbgdns/RULE-AVO.PNG?dl=0. herewith attached the role of the furies list. – PETER

1 Answers

1
votes

You need some transformation in Power Query Editor to apply to achieve the required output. Here below is the code from Advanced Editor-

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"

Here is the output-

enter image description here

As the code/steps are huge, please find the report file from this Link. Please check Transformation for table REPORT (2) and DATA (2)