Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
In that case go with this:
Proud to be a Super User!
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)
Proud to be a Super User!
Thank you! @ValtteriN
I hope I understood it correctly.
This is how my formula looks like.
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!
@ValtteriN Thank you!
I understood that now.
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:
Here I use a column from 'Temperature' and get a mathing value from 'Match'
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.
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
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!
@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:
Proud to be a 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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |