Forum Discussion
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 | 105 | 105 |
| 2 | March | 245 | 245 | 245 |
| 2 | April | 500 | 500 | 500 |
| 2 | May | 745 | 745 | 745 |
| 2 | June | 990 | 990 | 990 |
| 2 | July | 1050 | 1050 | 1050 |
| 2 | August | 2000 | 2000 | 2000 |
| 2 | September | 2100 | 2100 | 2100 |
| 2 | October | 2560 | 2343 | 2560 |
| 2 | November | 3400 | 3566 | 3045 |
| 2 | December | 4600 | 3789 | 4786 |
| 2 | January | 5000 | 4678 | 5678 |
| 40 | February | 105 | 105 | 105 |
| 40 | March | 245 | 245 | 245 |
| 40 | April | 500 | 500 | 500 |
| 40 | May | 745 | 745 | 745 |
| 40 | June | 990 | 990 | 990 |
| 40 | July | 1050 | 1050 | 1050 |
| 40 | August | 2000 | 2000 | 2000 |
| 40 | September | 2100 | 2100 | 2100 |
| 40 | October | 2560 | 2343 | 2560 |
| 40 | November | 3400 | 3566 | 3045 |
| 40 | December | 4600 | 3789 | 4786 |
| 40 | January | 5000 | 4678 | 5678 |
Can anyone please help me with this? as I am not able to get the max month like this anyhow.
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])
13 Replies
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")HI ryan_mayu
Thanks It worked for me.
Hi,
Do you have a year column as well? If yes, then please share the dataset with that column.
There is not year column, but I got the solution from ryan_mayu . Thanks for the help.
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?
