MAX function used inside a measure

[SimonM]

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 column 'CountMotivatedSector' which I am creating in the code. DAX doesn't seem to allow extracting the MAX value from a table creating in a measure.

Works fine if I create a calculated table through the 'new table' menu, any ideas what I am doing wrong or if there is a better way of acheiving this?

Thanks! 

Measure = 
VAR
VIRTUAL_TABLE = SUMMARIZE(TabMain,TabMain[Sector],"CountMotivatedSector",COUNTAX(FILTER('TabMain',[Segments]="1_Motivated"),[Segments]))
RETURN
MAX(ALL(CountMotivatedSector))

[Rémi · Accepted solution]

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

[Rémi · Accepted solution]

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 · Accepted solution]

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