How to Return Values from Matched Attributes using a 3rd Table

[wlf0032]

Hi,

I have 3 tables with relationships: Index Table (many <--1) Reference Table (1-->many) Benchmark Table.

How can I creat a measure in Index Table that, for each given Index, returns the corresponding value from the Benchmark table (for any given date)?

e.g. for date 2022-01-02, Index1, new measure should = 2.8.

Thank you very much!

Index Table

Date	Index	IndexValue
2022-01-01	Index1	2
2022-01-01	Index2	3
2022-01-02	Index1	2.5
2022-01-02	Index2	3.5

Benchmark Table

Date	Benchmark	BMValue
2022-01-01	BM1	2.2
2022-01-01	BM2	4
2022-01-02	BM1	2.8
2022-01-02	BM2	4

Reference Table

Index	Benchmark
Index1	BM1
Index2	BM2

[CNENFRNL · Accepted solution]

Simplest application of expanded table,

[wlf0032]

Thank you! Your messure works well if I just want to retrieve the corresponding BM value.

But I don't quite understand the logic of using "INDEX" table as a filter in CALCULATE. I tried to apply your template for another measure, which will get the cumulative product of the correspoding BM for the last 7 days, it failed to give the correct result. i.e. I tried:

CumulativeBM = CALCULATE(

                                  PRODUCT(BENCHMARK[BMValue]),

                                  INDEX,

                                  DATESINPERIOD(BENCHMARK[Date],MAX(INDEX[Date]),-7,DAYS))

How should I modify the formula here? Thank you!

[CNENFRNL]

As mentioned, it involves expanded table, a profound understanding of filter propagation in DAX. Here's a instructive post on this subject,

Expanded tables in DAX - SQLBI

This one works equally,

Your measure looks alright to me. Check relationships among tables in your data model. They're of extremely high importance.