Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How create a condition with LOOKUPVALUE

Hi! 

I have two different datas, as in the tables below

 

 

countrydatetypevalue
peru01/01/2023old5000
peru02/01/2022old4000
peru03/01/2022old8000
peru04/01/2022old3000
peru01/01/2023new1000
peru02/01/2022new8000
peru03/01/2022new500
peru04/01/2022new9000
USA01/01/2023old4000
USA02/01/2022old6000
USA03/01/2022old7000
USA04/01/2022old2000
USA01/01/2023new5000
USA02/01/2022new8000
USA03/01/2022new9000
USA04/01/2022new4000

 

countrydatevalue A
peru01/01/20234444
peru02/01/20228000
peru03/01/20229000
peru04/01/20228000
USA01/01/20235555
USA02/01/202212000
USA03/01/20226000
USA04/01/2022889

 

 

I tried add the column Value New using the code below, but it does not work

 

 

Value new = IF('data'[type] = "new", LOOKUPVALUE (

    'data'[value],

    'data'[date], [date]),
    'data'[country], [country], "0")

 

 

the end result would be this

countrydatevalue AValue OldValue new
peru01/01/2023444450001000
peru02/01/2022800040008000
peru03/01/202290008000500
peru04/01/2022800030009000
USA01/01/2023555540005000
USA02/01/20221200060008000
USA03/01/2022600070009000
USA04/01/202288920004000

 

 

 

 

 

6 Replies

  • SQL_SousChef's avatar
    SQL_SousChef
    Frequent Visitor
    2 years ago

    is the goal to have both a old value and new value? I am not sure I understand the question here but if you want the "new" value for each country/date then you can use this: 

     

    NewValue =
    SUMX ( FILTER ( data, data[type] = "new" ), data[value] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I tried this solution, but returns only the general sum of value for just type . I want the value sum by data, country and type. I would like to use LOOKUPVALUE  (using arguments data and country) with filter by type

      I tried this too, but it doesn't work

      New = LOOKUPVALUE (

          'a1'[value],

          'a1'[date], a2[date],

          'a1'[country], a2[country],

          'a1'[type], "new")

      • SQL_SousChef's avatar
        SQL_SousChef
        Frequent Visitor
        2 years ago

        Can you try putting the country and date in a table then add the measure I provided. It should show you the new value for each date and country combo.

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Anonymous 
    i am done with different method
    your first table as a1 and ypur second table as a2 and and create relationship  bw date column

         

    create measure

    value old =
    CALCULATE(SUM(a1[value]),a1[type]="old",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))
     
     
    create another measure
    value new =
    CALCULATE(SUM(a1[value]),a1[type]="new",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))
     

     


     

     

     

    Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Are the other way that you don't need to create a relationship?

      For me, this way it doesn't work

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        hi, Anonymous 

        try below solution without using of relationship bw tables
        Measure (1).

        valueold =
        CALCULATE(SUM(a1[value]),
                   a1[type]="old",
                   KEEPFILTERS(a1[country2]=SELECTEDVALUE(a2[country1])&&
                                a1[date]=SELECTEDVALUE(a2[date1])
                              )
                 )

         


         

         Measure (2).
        valuenew =
        CALCULATE(SUM(a1[value]),
                     a1[type]="new",
                     KEEPFILTERS(a1[country2]=SELECTEDVALUE(a2[country1])&&
                                   a1[date]=SELECTEDVALUE(a2[date1])
                                )
                )

         

        which give you below outout 

         
        here i am not use relationship bw table

         

        i am provide link of  .pbix  file. refer Here 

         

        Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.