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] ) )

     

     

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

Hi, Omega

 

If you want to use DAX, this formula will help.

QTYMeasure =

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards

Dale

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