topic Re: How create a condition with LOOKUPVALUE in DAX Commands and Tips

How create a condition with LOOKUPVALUE

Anonymous — Fri, 06 Oct 2023 18:00:10 GMT

Hi!

I have two different datas, as in the tables below

country	date	type	value
peru	01/01/2023	old	5000
peru	02/01/2022	old	4000
peru	03/01/2022	old	8000
peru	04/01/2022	old	3000
peru	01/01/2023	new	1000
peru	02/01/2022	new	8000
peru	03/01/2022	new	500
peru	04/01/2022	new	9000
USA	01/01/2023	old	4000
USA	02/01/2022	old	6000
USA	03/01/2022	old	7000
USA	04/01/2022	old	2000
USA	01/01/2023	new	5000
USA	02/01/2022	new	8000
USA	03/01/2022	new	9000
USA	04/01/2022	new	4000

country	date	value A
peru	01/01/2023	4444
peru	02/01/2022	8000
peru	03/01/2022	9000
peru	04/01/2022	8000
USA	01/01/2023	5555
USA	02/01/2022	12000
USA	03/01/2022	6000
USA	04/01/2022	889

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

country	date	value A	Value Old	Value new
peru	01/01/2023	4444	5000	1000
peru	02/01/2022	8000	4000	8000
peru	03/01/2022	9000	8000	500
peru	04/01/2022	8000	3000	9000
USA	01/01/2023	5555	4000	5000
USA	02/01/2022	12000	6000	8000
USA	03/01/2022	6000	7000	9000
USA	04/01/2022	889	2000	4000

Re: How create a condition with LOOKUPVALUE

SQL_SousChef — Fri, 06 Oct 2023 19:47:25 GMT

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

Re: How create a condition with LOOKUPVALUE

Dangar332 — Sun, 08 Oct 2023 20:07:31 GMT

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.

Re: How create a condition with LOOKUPVALUE

Anonymous — Mon, 09 Oct 2023 13:56:42 GMT

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

Re: How create a condition with LOOKUPVALUE

Anonymous — Mon, 09 Oct 2023 13:58:45 GMT

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

For me, this way it doesn't work

Re: How create a condition with LOOKUPVALUE

SQL_SousChef — Mon, 09 Oct 2023 14:00:25 GMT

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.

Re: How create a condition with LOOKUPVALUE

Dangar332 — Fri, 13 Oct 2023 11:07:15 GMT

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.