Max Value Lookupvalue

[Anonymous]

Hi Experts

Hwo would you look up the max value of a column using Lookupvalue - my measure currently is as follows with a filter condition..

Measure = Lookupvalue(input[value],Input[Year],M[Year],Input[bb],M[bb],Input[Period],M[Period],Input[Terms],"BTMT")

I only want to return the max of the Input[Value]

[amitchandak]

@Anonymous , if this across two tables, then refer

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi Amit here is a sample file ignore the relationship as one to one its many to one on the bridge table ... and i want to take from the Input table the value of M1 and put that against M1 in the M table. The PBIX file is only show data for one month/period, but my master has mulitple period starting in 2012 to date.

[Vera_33]

Hi @Anonymous 

Where is the sample file?

[Anonymous]

https://www.dropbox.com/s/7vdna17ffee0pf1/Test_2.pbix?dl=0 

Ignore the one to one relationship between bridging table should be many to one as i have only provided one month worth of data. 

[Vera_33]

Hi @Anonymous 

As you are using two identical tables (only value column is different), I am confused about what you want...

A measure to get a Max value from input table?

Or a column to get Max value from input table?

[Anonymous]

Hi Vera. Elements of the two table i have show are the same. The rest of the column in both table are totally different.

I need a caculated column Value in the second table M to take the value for M1 where the Term type is BTMT Fee and put that value into a new column in table M. for M1 only... a measure is not going to work....

[Vera_33]

Hi @Anonymous 

Not sure if I understand it correctly...

Column = 
VAR T1 = FILTER(Input,Input[Year]=M[Year]&&Input[BB]=M[BB]&&Input[Time Period]=M[Time Period]&&Input[M Column]="M1"&&Input[Terms]="BTMT Fee")
RETURN
MAXX(T1,[Value])

[Anonymous]

yes nearly correct, can we just show for M1 only.....its repeating 10 for all M Periods...in M Column if possible....

[Vera_33 · Accepted solution]

oh...you want to overwrite current column in DAX? no...you are adding a new column...like this? How about using M?

Column = 
VAR T1 = FILTER(Input,Input[Year]=M[Year]&&Input[BB]=M[BB]&&Input[Time Period]=M[Time Period]&&Input[Terms]="BTMT Fee")
RETURN
IF(M[M Column]="M1",MAXX(T1,[Value]))

[Anonymous]

one last thing....can this be done in Power Query.... exactly the same.....

https://community.powerbi.com/t5/Desktop/Convert-Measure-to-Custom-Column-in-Power-Query/td-p/176159...

[Anonymous]

Excellent.,....thank you god bless.

[Anonymous]

Hi Amit I'll post a file in the next hr