Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get Values from table1 for corresponding values from table2 with duplicates

I have 2 tables, table1 with ID and table2 with corresponding old ID. I want a calculation where  ID from table1 and correspoding OLD ID in table2 need to consider and then need values(quantity, pric...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Create 2 columns as below:

    _count = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
    _sum = 
var _qty=CALCULATE(MAX('Table1'[ Quantity]),FILTER('Table1','Table1'[Year]=2020&&'Table1'[ID]=EARLIER('Table1'[ID])))
var _price=CALCULATE(MAX('Table1'[Price]),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
Return
IF('Table1'[_count]=1,_qty*_price)

    And modify measure 2019 as below:

    Value(2019) = 
var _sum=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity])
var _price=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[Price])
var _aver=DIVIDE(_price,MAX('Table1'[_count]))
var _newID=CALCULATETABLE(VALUES('Table1'[new ID]),'Table1'[_count]>1)
Return
IF(MAX('Table1'[new ID]) in _newID,_sum*_aver,SUMX(SUMMARIZE('Table1','Table1'[ID],'Table1'[new ID],"price",MAX('Table1'[_sum])),[price]))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

v-kelly-msft Thanks for your help but as I mentioned there are duplicates in Table2 (Old ID) column as well. Consider a new row with values (7, 6) in table 2 and a new row with values (7, 2020, 21, 0.15) in table1.  Now in table1, for ID 5 & 6 need to consider ID 3 but for ID 7 need to consider 6.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Anonymous ,

     

    Could you pls provide a complete sample data with expected output for test?Better with your logic.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-kelly-msft Consider below data set and calculations. Consider the changes highlighted in yellow. Also in the calculation for 2019, i need to multiply price of 2019 with qty of 2020. pls see calculation highlighted in red for better uderstanding.

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  Anonymous ,

         

        Create 2 columns as below:

        _count = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
        _sum = 
var _qty=CALCULATE(MAX('Table1'[ Quantity]),FILTER('Table1','Table1'[Year]=2020&&'Table1'[ID]=EARLIER('Table1'[ID])))
var _price=CALCULATE(MAX('Table1'[Price]),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
Return
IF('Table1'[_count]=1,_qty*_price)

        And modify measure 2019 as below:

        Value(2019) = 
var _sum=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity])
var _price=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[Price])
var _aver=DIVIDE(_price,MAX('Table1'[_count]))
var _newID=CALCULATETABLE(VALUES('Table1'[new ID]),'Table1'[_count]>1)
Return
IF(MAX('Table1'[new ID]) in _newID,_sum*_aver,SUMX(SUMMARIZE('Table1','Table1'[ID],'Table1'[new ID],"price",MAX('Table1'[_sum])),[price]))

        And you will see:

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!