Forum Discussion
Solved
Max Column value based on another columns matching value
I have a table , where I want max month name where AL=CL=CU In this example September is the month which I want, regardless of any department Department Months AC CL CU 2 February 105 ...
pls try this
Measure = VAR tbl=ADDCOLUMNS('Table',"check",if('Table'[AC]='Table'[CL]&&'Table'[AC]='Table'[CU],1,0)) return FORMAT(maxx(FILTER(tbl,[check]=1),'Table'[Months]),"mmmm")pls try this
HI ryan_mayu
Thanks It worked for me.
pls try this
Measure = VAR _tbl = TOPN(1, SELECTCOLUMNS( FILTER( ADDCOLUMNS( 'Table', "Flag", INT( MIN( CALCULATE( MIN( 'Table'[AC] ) ), CALCULATE( MIN( 'Table'[CL] ) ) ) = CALCULATE( MIN( 'Table'[CU] ) ) ) ), [Flag] = 1 ), "@Month", 'Table'[Months], "@AC", 'Table'[AC] ),ABS([@AC])) RETURN MAXX(_tbl,[@Month])
pls try this
Hi Ahmedx
Thanks for the solution, this solution also worked for me
HI Ahmedx , ryan_mayu
If we want to create measure for same on negative numbers and for each department, how can we do that , I am not able to figure that out. Here is a sample data.Department Months AC CL CU 2 January 5000 4678 5678 2 December 4600 3789 4786 2 November 3400 3566 3045 2 October 2560 2343 2560 2 September 2100 2100 2100 2 August 2000 2000 2000 2 July 1050 1050 1050 2 June 990 990 990 2 May 745 745 745 2 April 500 500 500 2 March 245 245 245 2 February 105 105 105 46 February -1 -1 -1 46 March -2 -2 -2 46 April -3 -3 -3 46 May -4 -4 -4 46 June -5 -5 -5 46 July -6 -6 -6 46 August -7 -7 -7 46 September -8 -8 -8 46 October -9 -10 -11 46 November -10 -12 -13 46 December -14 -17 -15 46 January -20 -16 -15 and the result should be different, not September?
yes, depends on data, for negative right now it is september only, as max value works differently in negative right?