Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
@Saxon10 , my bad, I've corrected the link and now the right file is at your disposal now.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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?
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
could you please advise anyone regarding the M code logic?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |