Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 6
Dangar332
Super User
Super User

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

Dangar332_0-1696795240779.png     

create measure

value old =
CALCULATE(SUM(a1[value]),a1[type]="old",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))
 Dangar332_2-1696795455201.png
 
create another measure
value new =
CALCULATE(SUM(a1[value]),a1[type]="new",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))
 
Dangar332_4-1696795564148.png

 


 

 
Dangar332_1-1696795430696.png

 

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

 

 

Anonymous
Not applicable

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

For me, this way it doesn't work

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])
                      )
         )
Dangar332_3-1697194916903.png

 


 

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

 

which give you below outout 

Dangar332_0-1697194859542.png
 
here i am not use relationship bw table
Dangar332_4-1697195035287.png

 

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.

SQL_SousChef
Frequent Visitor

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
Not applicable

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

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.