Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to 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])
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |