Index match multiple criteria -DAX measure and M language

[Saxon10]

Hi,

I have a two tables are TableA and TableB, both tables contain text and number. The follwing colums are in TableA Item, Team, Place code and District code and TableB Team, Place code and District code. In both tables the following Headers are same, Team, Place code and District code and TableB Team.

In Excel I am applying below mentioned formulas(without duplication colums)  in order to achive my final result.

Column E formula -  '=IFERROR(IF(MATCH(B2,INDEX(TABLEA!$A$2:$A$52,0),0),"MATCHED"),"ERROR")-

Column F formula -  '=IFERROR(IF(MATCH(B2&"-"&C2,INDEX(TABLEA!$A$2:$A$52&"-"&TABLEA!$B$2:$B$52,0),0),"MATCHED"),"ERROR")

Column G formula'=IFERROR(IF(MATCH(B2&"-"&C2&"-"&D2,INDEX(TABLEA!$A$2:$A$52&"-"&TABLEA!$B$2:$B$52&"-"&TABLEA!$C$2:$C$52,0),0),"MATCHED"),"ERROR")

Formula Logic;

Column E-Match criteria is Team in both tables

Column F-Match criteria is Team, Place code in both tables

Column G-Match criteria is Team, Place code and District Code in both tables

Now my question is how can I achive the same result in Power BI without duplication/addtional columns.

Can you please advise the solution DAX measure also M language option as well.

I don't want use the merge option because it will create a lot of duplication columns in original data and meantime my original data lot of columns therefore I would like to achive my result by using DAX measure also M language.

https://www.dropbox.com/s/s3lyb7ss21myf4a/DATA-INDEX-1.PNG?dl=0

https://www.dropbox.com/s/rointtqrt2ajt51/INDEX%20MATCH-DAX-M.xlsx?dl=0

Could you please advise.

[CNENFRNL · Accepted solution]

Hi, @Saxon10 , just for fun, I came up with a spectrum of solutions with M, DAX ( measure and calculated column), as well as Excle formulae. You might want to refer to the attach file for all more details.

M solution in query "TableB Chk"

DAX solutions: measure / calculated column

      

Last but not least, Excel formulae, our good old pals ... I also attached the Excel file.

TEAM CHK =IF(COUNTIF(TableA[TEAM],[@TEAM]),"MATCHED","UNMATCHED")

PLACE CODE CHK {=IF(COUNTIFS(TableA[TEAM],[@TEAM],TableA[PLACE CODE],""&[@[PLACE CODE]]),"","ERROR")}
Array formula

DISTRICT CODE CHK {=IF(OR((TableA[TEAM]=[@TEAM])*(TableA[PLACE CODE]=[@[PLACE CODE]])*(TableA[DISTRICT CODE]=[@[DISTRICT CODE]])),"","ERROR")}
Arry formula

[Saxon10]

Hi,

Thanks for your Power BI and Excel soltion.

I think you attached the wrong Power BI file(file name is 24) also I am unable to see the code for M language and DAX measure in the sanpshot so could you please share the actual Power BI output file so I can check my end and I will update the feedback as soon as possibile.

I like your Excel logic and it's very simple. I not familer about array formula. I learn somting new in Excel. 

[CNENFRNL]

@Saxon10 , my bad, I've corrected the link and now the right file is at your disposal now.

[Saxon10]

Hi,

No problem at all. Thanks for attaching the actual file.

Measure:

Your measure solution working well. Thank you so much for that. Quick clarification here, if I have a blanks columns in TableB then need to be added any addtional function?

M Language;

In order to apply the following M-code "if List.Contains(TableA[TEAM], [TEAM]) then "MATCHED" else "ERROR" in custion what else I need to do before? I can see your file you created measure as well but I can't see any data and the measure is hide? can you please advise?

[Saxon10]

Hi,

I can see the measure column as a import data but it's not showing any value or output and How do you added Measure in advance query editor? Could you please share the info step by step so I can try to apply the same thing at my end. 

Please ref the snapshot

https://www.dropbox.com/s/ywa169iy991g150/qe-1.PNG?dl=0

https://www.dropbox.com/s/ywa169iy991g150/qe-1.PNG?dl=0

[Saxon10]

could you please advise anyone regarding the M code logic?