Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |