cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonM
Regular Visitor

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

 

2 ACCEPTED SOLUTIONS
Rémi
Resolver III
Resolver III

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

 

View solution in original post

SimonM
Regular Visitor

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

 

 

 

 

View solution in original post

2 REPLIES 2
Rémi
Resolver III
Resolver III

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

 

View solution in original post

SimonM
Regular Visitor

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

 

 

 

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors