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
Thanks for confirming first one is completed. Can you please add sample data for 3rd table ?
FinalTable has:
Key Column1 Column2 Cycle
1 John Doe 2
3 Jane Doe null
ReportTable has
Key Column1a Column2a
1 John Doe
5 Alex Doe
I'd like to have ReportTable as
Key Column1a Column2a Cycle
1 John Doe 2
5 Alex Doe null
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 assume this is a measure on the ReportTable ?
Hello EaglesTony ,
No, this was adding column to 3rd table. To convert into measure please change as per below dax :
// Please change table names from T_2 to FinalTable and T_3 to ReportTable
MeasureValue =
CALCULATE(
MAX(T_2[Cycle]), -
T_2[Key] = SELECTEDVALUE(T_3[Key]) -- Matching the Key from T_2 and T_3
)Cheers
The problem I have now is, that I use cycle in a slicer and when i try to add "Both" in the relationship between these 2 tables, it says it is a circular dependency(I assume since both values are calculated) ?????
Hello EaglesTony ,
Let's make it more clear. You have now 2 tables
1. Final Table with columns Key ,Date and Cycle
2. ReportTable with Key and other columns
3. A mesure for Cycle
You want to show values from FinalTable and ReportTable and Cycle as a slicer.
Have you checked if there is relationship setup between FinalTable and ReportTable ? If not, create a relationship on Key and try again.
I am able to use cycle as slicer to show values from both the tables (kindly ignore blank value , this is for testing only).
I hope this helps.
What i have is:
1) FinalTable with Key and Cycle
2) ReportTable that has Key and Cycle (based off a lookup to FinalTable)
3) Slicer that is using a Cycle field from a Cycle table
What I am trying to do is build a relationship between Cycle of the ReportTable to Cycle of the Cycle table, and have it as "Both", so that when the Slicer changes(Say Cycle 2), it will show all those records in ReportTable that have Cycle 2.
However when trying to establish this relationship it is giving me a circular reference.