Forum Discussion
How to Return Values from Matched Attributes using a 3rd Table
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 |
Simplest application of expanded table,
3 Replies
Simplest application of expanded table,
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!
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.
