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...
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

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

      Hi Cherie

       

      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.