POWER BI-If match multiple columns from one table to another table

0
votes

I have a two tables are Table1 and Table2, Table 1 is my data and Table 2 is Report. Table 1 and Table 2 contain the following headers "Fruit List", "Area Code" and Sales code". In Table 2 columns A to C are matched from Table 1 columns A to c then the status is "YES" if not matched then the status is "NO".

I can't use merge the query options here because it's creating lot of duplication line items. (I have unique data base around 322000 line items, when I try use the merge option it will create a lot duplicated line items) So I am looking for DAX measure or M language solutions options.

Example for line item 2; (In Table 2 Data) The item is 123 the fruit list is "Avocado" with area code is "3100" it's matched to my master data Table 1 therefore the status is "YES".

Example for line item 5; (In Table 2 Data) The item is 126 the fruit list is "Avocado" with area code is "West" it's not matched to my master data Table 1 therefore the status is "NO".

The "Avocado" "area code" belongs to only the following sales code (EU01,US,UK).

Example for line item 19; The item is 56 the fruit list is "watermelon" doesn't have a area code. Please refer the above mentioned snapshot.

Match criteria: The matching selection criteria is "Fruit list", "area code" and "sales code" in both tables.

Herewith attached the file for your reference.

https://www.dropbox.com/s/a2a4fqng58c4z59/IF%20MATCH%20ONE%20SHEET%20TO%20ANOTHER%20SHEET.pbix?dl=0 https://www.dropbox.com/s/oebfzind62o68fg/IF%20MATCH.xlsx?dl=0 enter image description here

1
daxpowerbi-desktopmeasure

1 Answers

0
votes

Create relationship betweent this tabel. First create column in TABLE1:

JoinCol = COMBINEVALUES(" ",'TABLE1'[AREA CODE],'TABLE1'[SALES CODE],'TABLE1'[FRUIT LIST])

in TABLE2:

JoinCol = JoinCol = COMBINEVALUES(" ",'TABLE2'[AREA CODE],'TABLE2'[SALES CODE],'TABLE2'[FRUIT LIST])

enter image description here

Then you Can make easily any calculation for example in Table2:

Output = if(LOOKUPVALUE(TABLE1[JoinCol],TABLE1[JoinCol],TABLE2[JoinCol]) = BLANK(),"NO","YES")

enter image description here

You can try also use UNION with INTERSECT and EXCEPT:

Table = UNION(
ADDCOLUMNS(INTERSECT(SUMMARIZECOLUMNS(TABLE1[FRUIT LIST],TABLE1[AREA CODE],TABLE1[SALES CODE]), SUMMARIZECOLUMNS(TABLE2[FRUIT LIST],TABLE2[AREA CODE],TABLE2[SALES CODE])), "IF?","YES"),

ADDCOLUMNS(EXCEPT(SUMMARIZECOLUMNS(TABLE2[FRUIT LIST],TABLE2[AREA CODE],TABLE2[SALES CODE]),SUMMARIZECOLUMNS(TABLE1[FRUIT LIST],TABLE1[AREA CODE],TABLE1[SALES CODE])),"IF?","NO"))