cancel
Showing results for
Did you mean:
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
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])`

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

2 REPLIES 2
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])`

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

Announcements

#### 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.