Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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'

Megha3012_2-1643290273334.png

 

Table 'Part Analysis'

Megha3012_1-1643290240891.png

 

Any help is appreciated!

 

Thank you!

Megha

 

 

1 ACCEPTED SOLUTION

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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.

 

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)





Did I answer your question? Mark my post as a solution!

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

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]





Did I answer your question? Mark my post as a solution!

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
 
 

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:

ValtteriN_0-1643298147259.png

 

ValtteriN_1-1643298164350.png

 

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:
ValtteriN_2-1643298330490.png

 

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







Did I answer your question? Mark my post as a solution!

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'

PlanningCalenderOrder cycle

2201-004

(to be filled from the other table)
2201-007 
2201-007 

 

Table 'Planning calender'

NameOrder cycle
2201-0047
2201-0057
2201-0063
2201-0074

 

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

 

Can I know why's that?

 

Thank you!

Megha

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?





Did I answer your question? Mark my post as a solution!

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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks a lot! @ValtteriN 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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