Forum Discussion
How do I get a value from another table based on a date range
Hello EaglesTony ,
Simple use Lookup between FinalTable and ReportTable to get the value :
Cycle =LOOKUPVALUE(FinalTable [Cycle], -- Column to returnFinalTable [Key], ReportTable [Key] -- Matching Key from both tables)I have checked this with sample data :I hope this is what you are looking for .
Did I solve your Query ? Please mark this as solution. Appreciate Kudos always
Cheers
I like this approach and seems to work.
How now do I get this Cycle value to another table in DAX both tables have Key as a column.
If I now have the following in a table called Table2:
Key Date Cycle
1 4/20/2022 2
2 5/25/2022 4
I have table1 with(notice no Key 2):
Key Column1 Column2
1 John Doe
3 Jane Doe
I need table1 to be:
Key Column1 Column2 Cycle
1 John Doe 2
3 Jane Doe null
Hello,
To bring the Cycle value from Table2 into Table1 you can create a calculated column in Table1 that looks up the corresponding Cycle from Table2 using the common Key column.
Create a Relationship:
Ensure that there is a relationship between Table1 and Table2 using the Key column. If the relationship doesn’t already exist, go to the Model view and create a one-to-many relationship from Table1[Key] to Table2[Key].
Create a Calculated Column in Table1:
Go to Modeling and select New column.
Write the following DAX formula to create a calculated column that brings in the Cycle value from Table2:
Cycle = RELATED(Table2[Cycle])
The RELATED function is used to fetch a value from a related table. In this case, it retrieves the Cycle from Table2 where the Key matches.
Result:
After applying the formula, Table1 will now have a new Cycle column that contains the matching Cycle from Table2 for each Key. If there is no matching Key, it will return null.
Let me know if you have any more questions!
I want to use this approach.
Using DAX, I was able to get the correct cycle into Table1.
Now my final part is to get that column into a FinalTable that is used on a report. However, FinalTable has more records than Table1, so there might not be a matching Key from FinalTable in Table1.