Forum Discussion

SimonM's avatar
SimonM
Regular Visitor
10 years ago
Solved

MAX function used inside a measure

  Hi everyone, I'm fairly new to DAX but just trying to do something simple - getting the MAX value from a table that is created within a measure, my code is below.  The grouped by values are in the...
Need Help
  • Rémi's avatar
    Rémi
    10 years ago

    Hi,

     

    I think that it's a small mistake from you, can you try this for your measure :

    MAX(ALL('VIRTUAL_TABLE'[CountMotivatedSector]))

     

    Otherwise, I think that you don't have to create a table just for a measure, you can directly extract the max :

    Max Value = MAXX(SUMMARIZE(TabMain,TabMain[Sector],"CountMotivatedSector",COUNTAX(FILTER('TabMain',[Segments]="1_Motivated"),[Segments])),[CountMotivatedSector])

     

  • SimonM's avatar
    SimonM
    10 years ago

    Hi Rémi, thanks so much for that! I get it now. I had already created the "CountMotivatedSector" column as a summary count within the SUMMARIZE function, what I didn't realise was that I could nest it inside a MAXX function and then reference that column in the second argument of the MAXX function just as you would a regular column - using the [CountMotivatedSector] identifier.

     

    Your syntax below works great, struggled for hours yesterday so thanks again! Now the measure dynamically changes as I filter the report which is exactly what I needed to do.

     

    Max Value = MAXX(SUMMARIZE(TabMain,TabMain[Sector],"CountMotivatedSector",COUNTAX(FILTER('TabMain',[Segments]="1_Motivated"),[Segments])),[CountMotivatedSector])

    Simon

     

     

     

     

Rémi's avatar
Rémi
Resolver III
10 years ago

Hi,

 

I think that it's a small mistake from you, can you try this for your measure :

MAX(ALL('VIRTUAL_TABLE'[CountMotivatedSector]))

 

Otherwise, I think that you don't have to create a table just for a measure, you can directly extract the max :

Max Value = MAXX(SUMMARIZE(TabMain,TabMain[Sector],"CountMotivatedSector",COUNTAX(FILTER('TabMain',[Segments]="1_Motivated"),[Segments])),[CountMotivatedSector])

 

  • SimonM's avatar
    SimonM
    Regular Visitor
    10 years ago

    Hi Rémi, thanks so much for that! I get it now. I had already created the "CountMotivatedSector" column as a summary count within the SUMMARIZE function, what I didn't realise was that I could nest it inside a MAXX function and then reference that column in the second argument of the MAXX function just as you would a regular column - using the [CountMotivatedSector] identifier.

     

    Your syntax below works great, struggled for hours yesterday so thanks again! Now the measure dynamically changes as I filter the report which is exactly what I needed to do.

     

    Max Value = MAXX(SUMMARIZE(TabMain,TabMain[Sector],"CountMotivatedSector",COUNTAX(FILTER('TabMain',[Segments]="1_Motivated"),[Segments])),[CountMotivatedSector])

    Simon