Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
106 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |