Forum Discussion
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 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))
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])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
2 Replies
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])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