Forum Discussion

hari_bi's avatar
hari_bi
Icon for Helper I rankHelper I
8 years ago

Excel Index match in DAX

Excel Formula for above one : =INDEX($AM$9:$AM$20,MATCH(D15,$E$9:$E$20,0))

 

Now i need to implement the same in DAX Calculated Measure.

First i need to get month from "ReportingMoth" cloumn then i need to search same moth in "FiscalMonth" column after taht need to get respected moth value from "monthsCover " column and finally need to display the same in value new column "MothsCoverMatch".

 

Here MothCover := Actual/Total is Calcualted Meaure.

Now i need to do same in Dax as Calcualted Measure/Column, Please provide the suggestinos to achieve this one.

 

13 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi hari_bi

    You may try to create a summarize table first. Then use LOOKUPVALUE Function to create a column. For example:

    Table =
SUMMARIZE ( Table2, Table2[Fiscal Month], Table2[Months Cover] )
    Column =
LOOKUPVALUE (
    'Table'[Months Cover],
    'Table'[Fiscal Month], Table2[Reporting Month]
)

    Regards，

    Cherie

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi hari_bi

         

        You may try below measure. If it is not your case,  share your file in Dropbox and post the link here.

        lookup =
CALCULATE (
    VALUES ( Table2[Months Cover] ),
    FILTER ( ALL ( Table2 ), Table2[Fiscal Month] = Table2[Reporting Month] )
)

         

        Regards,

        Cherie

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Write this calculated column formula

     

    =CALCULATE(SUM(Data[Months Cover]),FILTER(Data,Data[Fiscal Month]=EARLIER(Data[Reporting Month])))

     

    Hope this helps.

    • hari_bi's avatar
      hari_bi
      Icon for Helper I rankHelper I
      8 years ago

      Hi

       

      Thanks for your replay.

       

      https://www.dropbox.com/s/lnvnttc4tvmiq7k/Local%20Copy%20R%26D.pbix?dl=0

       

      Please find the Index Match page, my query is available on table visual.

      Months Cover will avaialebe in Depletions Table and months cover is a calcualted measure not a base measure.

      Fiscal Month (Fiscal Month) available in Date table

      Reporting Month available on Forecast Date table.

       

      Thank you

      Hari.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        I would like to help but am unable to because of the confusion caused by so many other Tables in your file.  Just share only as much information as is necessary to solve the problem.  Remove the unwanted Tables/relationships and only share the tables which are required for solving the problem.