Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Solved! Go to 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 , 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.
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?
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?
Or a column to get Max value from input table?
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...
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])
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]))
one last thing....can this be done in Power Query.... exactly the same.....
Excellent.,....thank you god bless.
Hi Amit I'll post a file in the next hr
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |