Max Column value based on another columns matching value

[Jessica_17]

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. 

[ryan_mayu · Accepted solution]

@Jessica_17 

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")

[Ahmedx · Accepted solution]

pls try this

[Jessica_17 · Accepted solution]

HI @ryan_mayu 

Thanks It worked for me.

[Ahmedx · Accepted solution]

pls try this

[Jessica_17]

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

[Ahmedx]

and the result should be different, not September?

[Jessica_17]

yes, depends on data, for negative right now it is september only, as max value works differently in negative right?

[Ahmedx]

I don't think I understand you but try again

[Jessica_17]

HI @Ahmedx 

Suppose for this department , last matching values are -8 for september month, while using you query, it will show february, but I want september 

as in negative value we would want minimum value instead of maximum value.

[Ahmedx · Accepted solution]

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])

[Jessica_17]

Hi @Ahmedx 

This worked, thank you so much for the help.

[Jessica_17]

Hi @Ahmedx 

Thanks for the solution, this solution also worked for me

[Ashish_Mathur]

Hi,

Do you have a year column as well?  If yes, then please share the dataset with that column.

[Jessica_17]

Hi @Ashish_Mathur 

There is not year column, but I got the solution from @ryan_mayu . Thanks for the help.

[ryan_mayu · Accepted solution]

@Jessica_17 

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")

[Jessica_17 · Accepted solution]

HI @ryan_mayu 

Thanks It worked for me.