If match index across the columns then Yes or no

[Anonymous]

Hi,

I am new to power BI and this is my first post. Could you please help me the below mentioned query.

I have two tables here, Table1 is Master Data, Table2 is my report data(output data). I want output in Table2 against Table1. If match the criteria from master data(Table1) to Table2 (report data) then return the status is "Yes" if not then return the staus is "No".

Please advise alternative ways to achive my result (Like a formula in power Bi or table relasionship etc..).

How can attached the file here could you please share the link.  

ITEM	ID	AREA CODE	QTY	LANGUAGE	SALES CODE	STATUS	ID	AREA	QTY	LANGUAGE	SALES CODE
123	RAMBUTAN	3100	100	EN	EU01	OKAY	-	-	-	-	-
123	RAMBUTAN	3100	100	EN	US	OKAY	-	-	-	-	-
123	RAMBUTAN	3100	100	EN	UK	OKAY	-	-	-	-	-
123	RAMBUTAN	WEST	100	EN	UK	ERROR	-	ERROR	-	-	-
123	RAMBUTAN	3100	1	EN	UK	ERROR	-	-	ERROR	-	-
123	RAMBUTAN	3100		EN	UK	ERROR	-	-		-	-
123	RAMBUTAN	3100	100		UK	ERROR	-	-	-	ERROR	-
123	RAMBUTAN	3100	100	EN	INDIA01	ERROR	-	-	-	-	ERROR
123	RAMBUTAN	3100	100	EN		ERROR	-	-	-	-
234	STAR FRUIT	WEST	200	EN	INDIA01	OKAY	-	-	-	-	-
234	STAR FRUIT	NORTH	100	EN	INDIA01	OKAY	-	-	-	-	-
234	STAR FRUIT	EAST	43	EN	INDIA01	OKAY	-	-	-	-	-
234	STAR FRUIT	SOUTH	56	EN	INDIA01	OKAY	-	-	-	-	-
234	STAR FRUIT	WEST	78	EN	INDIA02	OKAY	-	-	-	-	-
234	STAR FRUIT	NORTH	99	EN	INDIA02	OKAY	-	-	-	-	-
234	STAR FRUIT	EAST	133	EN	INDIA02	OKAY	-	-	-	-	-
234	STAR FRUIT	SOUTH	45	EN	INDIA02	OKAY	-	-	-	-	-
56	WATERMELON		100	EN	UK	OKAY	-	-	-	-	-

FRUIT LIST	AREA CODE	QTY	LANGUAGE	SALES CODE 1	SALES CODE 2	SALES CODE 3
RAMBUTAN	3100	1	EN	EU01	US	UK
RAMBUTAN	3200	1	EN	EU01	US	UK
RAMBUTAN	3300	1	EN	EU01	US	UK
RAMBUTAN	4500	1	EN	EU01	US	UK
RAMBUTAN	8900	1	EN	EU01	US	UK
STAR FRUIT	WEST	1	EN	INDIA01	INDIA02	NA
STAR FRUIT	NORTH	1	EN	INDIA01	INDIA02	NA
STAR FRUIT	EAST	1	EN	INDIA01	INDIA02	NA
STAR FRUIT	SOUTH	1	EN	INDIA01	INDIA02	NA
BANANA	KP1	1	EN	XX1
BANANA	PP1	1	EN	XX2
APPLE	444	1	EN	BAN03
ORANGE	555	1	EN	BAN03
ORANGE	555	1	EN	BAN04
ORANGE	555	1	EN	BAN05
PLUM	123	1	EN	BAN03
WATERMELON		1	EN	EU01	US	UK

[mhossain]

@Anonymous 

What are the columns you are relating in two tables? You can try 'Merge' option as well in the powerquery.

[Anonymous]

thanks for your reply. I can't use the merge query becuase of my sales code contaion one column but in my master data shett 3 columns. Please advise how can I merge the query in this case?

[Anonymous]

Hi,

I achived my result in excel using the sumporduct formula.

In G2=IF(AND(SUMPRODUCT((TABLE1!A$2:A$18=B2)*(TABLE1!B$2:B$18=C2))>0,IF(B2="Banana",D2=1,D2>1),SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!D$2:D$18=E2))>0,SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!E$2:G$18=F2))>0),"YES","NO")

How can I achive the sumproduct in power BI.

Attached the excel file for your reference.

https://www.dropbox.com/scl/fi/yzwky0n1gghp76nifphjj/POER-BI-DATA.xlsx?dl=0&rlkey=f0tm0awk7arxuohz3z...

[amitchandak]

@Anonymous , new column in Table 2

if(isblank(related(Table1[Col1])), "No", "Yes")

[Anonymous]

Hi,

Thanks for your quck reply.

It's not working. Attached the snapshot of the error. Could you please advise how can I attached the file in this forum.

[amitchandak]

@Anonymous , This can work a new column in Table 2, when Table 1 and Table 2 are related in 1 to M.

Try this , new column in table 2

if(isblank(countx(filter(Table1, Table1[key] = table2[Key]),Table1[Key])), "No", "Yes")

[Anonymous]

Hi,

Sorry still it's not working. I am new to Power BI. Could you able to share the file or advise how can I attached the file?

Exactly I am following the same step as you advised but still I got the error message. What mean by "Key" in your formula? Please refer the snapshot for your reference.

[Anonymous]

any advise please.

[v-deddai1-msft]

Hi @Anonymous ,

>>If match the criteria from master data(Table1) to Table2 (report data) then return the status is "Yes" if not then return the staus is "No".

Would you please explain more about logic of matching the criteria from master data(Table1) to Table2 (report data)? 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi,

Thanks for your reply and sorry I am not explain clarly.

This is Table 2 data base.

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 therfore the status is "NO".

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

If any error while matching (Fruit list, area code, qty, language, saltes code) the two tables from Table 2 to Table 1 then retuen the error message according to the Header. The item 126 is the area code is error becuase the area code not matched to my master data Table 1.

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 therfore the status is "YES". There is no error here therefore need a blanks according to the header.

Match criteria: Here the selection criteria in both tables "Fruit list, area code + area code" in ordr to compare or match the following columns in both tables "qty, language, saltes code".

In Table 2 columns from B to F need to be matched from Table 1 columns from A to G. If matched then the status is "YES" if not then the status is "NO".

Qty columns conditions; The qty columns shondn't be blanks or 0. it's always greater than 1 expect the Banana with area code "PP1 and "KP1" its always equal to 1.

Sales code conditions; The sales code columns shondn't be blanks or 0.

Watermelon;

Note; Watermelon doesn't have a area code. Please refer the output snapshot.

 Could you please let me know if you need a more information also please advise how can share or attached the file?

Table 2(Output sheet);

ITEM	FRUIT LIST	AREA CODE	QTY	LANGUAGE	SALES CODE	STATUS	ID	AREA	QTY	LANGUAGE	SALES CODE
123	Avocado	3100	100	EN	EU01	YES
124	Avocado	3100	100	EN	US	YES
125	Avocado	3100	100	EN	UK	YES
126	Avocado	WEST	100	EN	UK	NO		ERROR
127	Avocado	3100	1	EN	UK	NO			ERROR
128	Avocado	3100		EN	UK	NO			ERROR
129	Avocado	3100	100		UK	NO				ERROR
130	Avocado	3100	100	EN	INDIA01	NO					ERROR
131	Avocado	3100	100	EN		NO					ERROR
234	Star Fruit	WEST	200	EN	INDIA01	YES
235	Star Fruit	NORTH	100	EN	INDIA01	YES
236	Star Fruit	EAST	43	EN	INDIA01	YES
237	Star Fruit	SOUTH	56	EN	INDIA01	YES
238	Star Fruit	WEST	78	EN	INDIA02	YES
239	Star Fruit	NORTH	99	EN	INDIA02	YES
240	Star Fruit	EAST	133	EN	INDIA02	YES
241	Star Fruit	SOUTH	45	EN	INDIA02	YES
56	Watermelon		100	EN	UK	YES

Table 1(Master Data);

FRUIT LIST	AREA CODE	QTY	LANGUAGE	SALES CODE 1	SALES CODE 2	SALES CODE 3
Avocado	3100	1	EN	EU01	US	UK
Avocado	3200	1	EN	EU01	US	UK
Avocado	3300	1	EN	EU01	US	UK
Avocado	4500	1	EN	EU01	US	UK
Avocado	8900	1	EN	EU01	US	UK
Star Fruit	WEST	1	EN	INDIA01	INDIA02	NA
Star Fruit	NORTH	1	EN	INDIA01	INDIA02	NA
Star Fruit	EAST	1	EN	INDIA01	INDIA02	NA
Star Fruit	SOUTH	1	EN	INDIA01	INDIA02	NA
Banana	KP1	1	EN	XX1	NA	NA
Banana	PP1	1	EN	XX2	NA	NA
Apple	444	1	EN	BAN03	NA	NA
Orange	555	1	EN	BAN03	NA	NA
Orange	555	1	EN	BAN04	NA	NA
Orange	555	1	EN	BAN05	NA	NA
Plum	123	1	EN	BAN03	NA	NA
Watermelon		1	EN	EU01	US	UK

[Anonymous]

Hi,

Could you please advise anyone or shall I close the thread.