Get max value out of a table in measure

[Pbiuserr]

Hello,
I need to create measure which retrive max value of "Change" from the table like down below but for females - so by gender and location. It would be 233.3% ( Location 77 and Female) or if not possible - from entire table so 371.4%

I assume this table needs to be constructed within a measure by for instance ADDCOLUMNS, but I really struggle to do that

Can anybody help me with that? I think its kind of advanced DAX

[v-yanjiang-msft]

Hi @Pbiuserr ,

According to your description, here's my solution.

Create a measure.

Top = MAXX(FILTER(ALL('Table'),'Table'[Gendar]="Female"),'Table'[Change])

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pbiuserr · Accepted solution]

The solution I made is

VAR _table =

FILTER(

SUMMARIZE ( PersAndJobInfo, Fct_Table[Gender], Fct_Table[Location] ,

"@Chng", [Change]),

Fct_Table[Gender] = "Female")

VAR _top_value =

MAXX( _table, [@HC] )

RETURN

_top_value

[AilleryO]

Maybe you should try using SUMMARIZECOLUMNS and ROLLUPADDISSUBTOTAL to make your temp table and then look for the max in it with a filter.

SUMMARIZECOLUMNS ( Table[Gender], Table[Location], "Change value" , [Change] )

[Pbiuserr]

I got an error that SummarizedColumns() and AddMissingItems() may not be used in that context

[AilleryO]

Hi,

I don't know if your source is structured the same as your visual but if it's the case this shloud do the job with a measure like :

CALCULATE ( MAX( [Change] ) , [Gender] = "Female" )

Tell us if it works.

If not can you show the source structure or even copy psate some dummy datas.

[Pbiuserr]

Hi, 
My aim is to create KPI card which includes maximum change value of female out of all location (and name of location but can be on separated card). If I do what you've presented I dont have filter context by location I guess, also  "Change" is a measure so I can't put it into MAX I guess as I need a column reference

Basically I got columns
Gender
Location

Measures
ValueThisYear (let it be some count, even count of ID)
Value previous year
Change so thisperiod-lastperiod/lastperiod

and I want to retrive max value of change split by gender and location (but in reality only for Female)
I dont yet have permission to insert a pbix/xlsx file here

Was thinking myself about 

Top val =

VAR _table =

ADDCOLUMNS(
VALUES(Fct_tbl[Gender]),

VALUES(Fct_tbl[Location]),

"@Chng", [Change]

)

VAR _top_value =

MAXX(_table, [@Chng] )

RETURN

_top_value

but somehow I can't structure the table this way 

[amitchandak]

@Pbiuserr , How about having a rank column and using that as visual level filter

Rankx(allselected(Fct_tbl[Gender],Fct_tbl[Location]),[Change],,desc,dense)

[Pbiuserr]

Hi @amitchandak 
I don't use it on visual, it supposed to be KPI card with only 1 value. What your measure did was ranked it by location and gender. I want retrive only one value - max, out of all [Change] values in Gender and Location context