cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Index and Match DAX

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

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

12 REPLIES 12
Anonymous
Not applicable

Thank you! @ValtteriN

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

Super User

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)

Proud to be a Super User!

Anonymous
Not applicable

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
Super User

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]

Proud to be a Super User!

Anonymous
Not applicable

@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

Super User

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'

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

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?

Proud to be a Super User!

Anonymous
Not applicable

@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

Super User

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

Proud to be a Super User!

Anonymous
Not applicable

Thanks a lot! @ValtteriN

Super User

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!

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors