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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Max Value Lookupvalue

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]

 

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

 

 

Vera_33_0-1617364955537.png

 

View solution in original post

12 REPLIES 12
amitchandak
Super User
Super User

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

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.

Hi @Anonymous 

 

Where is the sample file?

Anonymous
Not applicable

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. 

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?

Vera_33_1-1617361138414.png

Or a column to get Max value from input table?

Vera_33_0-1617361099264.png

 

Anonymous
Not applicable

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....

Hi @Anonymous 

 

Not sure if I understand it correctly...

 

Vera_33_0-1617364341062.png

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

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

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

 

 

Vera_33_0-1617364955537.png

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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