Forum Discussion

Omega's avatar
Omega
Impactful Individual
9 years ago
Solved

Merge tables using latest dates

Dears,    I am new to BI and I have a small challenge:   I have 3 tables: Sales, QTY and Mapping:   Sales:                             QTY:                         Mapping:       Item| Sales  ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Omega

     

    Hi,

    Maybe you didn’t create relationships among the tables. That could be why the sales is empty. Do the relationships look like this below?

    If so, you can use these two measures to get “item2” and “sales”.

    Item2Measure =

    CALCULATE ( VALUES ( Mapping[item2] ), LASTDATE ( Mapping[date] ) )

    QTYMeasure =

    CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )

     

     

Omega's avatar
Omega
Impactful Individual
9 years ago

Instead of getting the QTY from QTY table, how to get item 2 from mapping table? Meaning, I'll have two columns, one that have A and the other have Z.

 

Thanks!

Omega's avatar
Omega
Impactful Individual
9 years ago

v-jiascu-msft 

 

Hi Dale,

 

I tried your method but sales are empty. Don't know why :( 

 

Please advise. 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Omega

     

    Hi,

    Maybe you didn’t create relationships among the tables. That could be why the sales is empty. Do the relationships look like this below?

    If so, you can use these two measures to get “item2” and “sales”.

    Item2Measure =

    CALCULATE ( VALUES ( Mapping[item2] ), LASTDATE ( Mapping[date] ) )

    QTYMeasure =

    CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )

     

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Omega

     

    Hi,

     

    Did this work? What else can I do for you?

     

    Best Regards!

    Dale