cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Megha3012
Helper V
Helper V

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

@Megha3012 

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
Megha3012
Helper V
Helper V

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!




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!




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




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!




 @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

@Megha3012 

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!




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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors