Forum Discussion

Jessica_17's avatar
Jessica_17
Helper V
2 years ago
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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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's avatar
    Ahmedx
    2 years ago

    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's avatar
Jessica_17
Helper V
2 years ago

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

Ahmedx's avatar
Ahmedx
Super User
2 years ago

I don't think I understand you but try again

  • Jessica_17's avatar
    Jessica_17
    Helper V
    2 years ago

    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's avatar
      Ahmedx
      Super User
      2 years ago

      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's avatar
        Jessica_17
        Helper V
        2 years ago

        Hi Ahmedx 

        This worked, thank you so much for the help.