• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formula to DAX

Dear Ninjas,

I am new to power pivot, power query and power view. appreciate if any help to deal this below formula into DAX and please suggest any tutorial or guide for power query, power pivot and power view as well.

=INDEX(Sheet2!$D:$D,MATCH(TRUE,IF(Sheet1!A2=Sheet2!A:A,ISNUMBER(SEARCH("store",Sheet2!$C:$C))),0))

Regards
Ramesh Deo
 
Upload a sample workbook please. With manually created expected output as well as source tables.

Typically, this type of lookup is done in PowerQuery stage (using "M") instead of in DAX. Though I'm sure you can create dimension table using Dax as well.
 
@Ramesh Deo :

By default DAX measures are for aggregation (ie summing, counting, averaging etc.). It seems, your INDEX formula is going to fetch a value from D:D when A:A matches A2 and C:C has the word "Store". As Chihiro suggested, this is best done in either Excel or PQ or as a calculated column in PP, but not as DAX. Again, without context, example workbook it is hard for us to judge the solution option. So either upload a workbook or go back to drawing board to see if PPDAX is the right way to approach this problem.

All the best.
 
Back
Top