topic Re: Index and Match DAX in DAX Commands and Tips

Index and Match DAX

Anonymous — Thu, 27 Jan 2022 13:34:47 GMT

Hi All,

This is the formula I have used in excel and I not sure how to in PowerBI.

=INDEX('Planning Calendar 122921'!$A$1:$J$71,MATCH('Part Analysis'!S359,'Planning Calendar 122921'!A:A,0),10)

Table 'Planning calender'

Table 'Part Analysis'

Any help is appreciated!

Thank you!

Megha

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 13:58:10 GMT

Hi,

You can achieve the same effect in DAX using variables. E.g.
[Get column value for a certain date] =
var _date = MAX('Calendar'[Date]) return
CALCULATE(MAX('Table'[Column]),ALL(Table),Table[Date]=_date)

Here we get a value which matches the date in our visual. You can add more variables (var) or modify the filter funtions (ALL) to get matches with specific conditions. Example: Get a value from a column with date value of 27.1.2022

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 14:35:18 GMT

Thank you! ValtteriN

Could you please elaborate?

I did not understand 'Get a value from a column with date value' and 'date' included in the DAX formula.

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 14:39:31 GMT

Hi,

So if you want to e.g. "MATCH" 2201-004 you would put column A in your MAX()

[Get column value for a certain date] =
var _get = MAX('Table2'[ColA]) return
CALCULATE(MAX('Table'[Column]),ALL(Table),Table[col]=_get)

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 15:14:35 GMT

Thank you! ValtteriN

I hope I understood it correctly.

This is how my formula looks like.

Order Cycle = var _get = MAX('Part Analysis'[PlanningCalendar]) return

CALCULATE(MAX('Planning calender'[Name]),ALL('Planning calender'),'Planning calender'[Order Cycle]=_get)

I am not sure if this is correct.

Thanks a lot for your help!

Megha

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 15:17:48 GMT

Hi,

This part of the DAX checks for a match: ,'Planning calender'[Order Cycle]=_get)

So you should have the date column there within the []

The MAX here: CALCULATE(MAX('Planning calender'[Name]

Is the value we return. So, put the [Order Cycle there]

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 15:29:25 GMT

ValtteriN Thank you!

I understood that now.

Order Cycle = Format(var _get = MAX('Part Analysis'[PlanningCalendar]) return

CALCULATE(MAX('Planning calender'[Order Cycle]),ALL('Planning calender'),'Planning calender'[Name]=_get),"").

I am sorry, but I still dont understand the date part, where and how to include that.

Thank you!
Megha

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 15:45:57 GMT

Hi,

The date part was just an example since I can't see the cell S359 in your second table. Here is a more elaborate example of the logic:

data:

Now we will fetch the data corresponding to a temperature:

IndexMatchTemperature = VAR _temp = MAX(temperature[Temperature]) return

CALCULATE(MAX(Match[Value]),ALL(Match),Match[Temperature]=_temp)

End result:

Here I use a column from 'Temperature' and get a mathing value from 'Match'

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 16:36:09 GMT

Thank you! ValtteriN

I understood now. Thanks so much!

But the values does not seem to match. Its different.

Order Cycle = var _get = MAX('Part Analysis'[PlanningCalendar]) return

CALCULATE(MAX('Planning calender'[Order Cycle]),ALL('Planning calender'),'Planning calender'[Name]=_get)

I have also given the data of my tables below:

Table 'Part Analysis'

PlanningCalender	Order cycle
2201-004	(to be filled from the other table)
2201-007
2201-007

Table 'Planning calender'

Name	Order cycle
2201-004	7
2201-005	7
2201-006	3
2201-007	4

The solution which is returning to me is '3' for all the rows.

Can I know why's that?

Thank you!

Megha

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 16:51:25 GMT

Hi,

The DAX in my example is for a measure. Are you also trying this with measure or do you want a calculated column and if so what is the reason behind this?

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 17:13:44 GMT

ValtteriN I am trying for calculated column.

There is no particular reason. I am not very proficient in DAX and I am not very familiar with just creating measures and use in further calculations.

Thank you!
Megha

Re: Index and Match DAX

ValtteriN — Thu, 27 Jan 2022 17:18:00 GMT

Anonymous

In that case go with this:

Order Cycle =
var _get = 'Part Analysis'[PlanningCalendar] return

CALCULATE(MAX('Planning calender'[Order Cycle]),ALL('Planning calender'),'Planning calender'[Name]=_get)

However I do recommend using measures when possible. They are more efficient.

Re: Index and Match DAX

Anonymous — Thu, 27 Jan 2022 17:28:30 GMT

Thanks a lot! ValtteriN