Forum Discussion

wlf0032's avatar
wlf0032
Frequent Visitor
4 years ago
Solved

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 correspo...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

Simplest application of expanded table,

wlf0032's avatar
wlf0032
Frequent Visitor
4 years ago

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's avatar
    CNENFRNL
    Community Champion
    4 years ago

    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.