cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Lookupvalue

Hi,

I want to transfer a value from one mapping table to another table using lookupvalue.
This value is date-dependent (valid from and valid to)
Mapping table:Anmerkung 2020-06-15 071207.png

 

Target Table:
Target.png

 

The date from the target table should lie between the valid to an valid from.

 

I also have to consider that the valid from and valid to can change at the beginning of a month.
Then a new column with a new valid from and valid to will be added.
I hope that someone can help me.
Thanks already and Kind regards
Till

 

 



2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Lookupvalue

@Till , Not Sure I got it

 

you can move data from one table to another using multiple conditions like this

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])

 

Maxx can be replaced with sumx,countx etc



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User III
Super User III

Re: Lookupvalue

@Till 

 

If you have serveral conditions, you can use && to join all of them.

 

Please try below measure

 

New Column = maxx(filter(map_inv,map_inv[value_1]= v_inv_full[Aktivität] && map_inv[value_2]= v_inv_full[Ebene]&&,v_inv_full[Datum]>=map_inv[valid_from]&&v_inv_full[Datum]<=map_inv[valid_to] ),map_inv[value_3])

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Lookupvalue

@Till , Not Sure I got it

 

you can move data from one table to another using multiple conditions like this

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])

 

Maxx can be replaced with sumx,countx etc



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User III
Super User III

Re: Lookupvalue

@Till 

 

I am not sure if I understand your request clearly. Please see if below info is helpful for you.

1.PNG

Column = MAXX(FILTER(TA,'TB'[date]>='TA'[validfrom]&&TB[date]<=TA[validto]),TA[project])

2.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Resolver I
Resolver I

Re: Lookupvalue

Hi
Thanks for your quick response.
I've implemented your proposal.

New Column = maxx(filter(map_inv,map_inv[value_1]= v_inv_full[Aktivität] && map_inv[value_2]= v_inv_full[Ebene] ),map_inv[value_3])

It works fine.
I now need to compare the date from the second table with the date from the first table.
Date between valid from and valid to.
See solution from ryan_mayu (Solution Sage) 

MAXX(FILTER(map_inv,v_inv_full[Datum]>=map_inv[valid_from]&&v_inv_full[Datum]<=map_inv[valid_to]),map_inv[value_3])


The two solutions would now have to be combined, which would be the solution

Thanks, also to ryan_mayu 




thanks for your help.

Till

Highlighted
Super User III
Super User III

Re: Lookupvalue

@Till 

 

If you have serveral conditions, you can use && to join all of them.

 

Please try below measure

 

New Column = maxx(filter(map_inv,map_inv[value_1]= v_inv_full[Aktivität] && map_inv[value_2]= v_inv_full[Ebene]&&,v_inv_full[Datum]>=map_inv[valid_from]&&v_inv_full[Datum]<=map_inv[valid_to] ),map_inv[value_3])

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Lookupvalue

It works and learned something new again.
Thanks to both of you
I wish you a nice week and stay healthy.

Kind regards 
Till

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors