Get a category name with minimum value

[Pbiuserr]

Hello, 

The measure down below shows how to get category name with maximum value

CALCULATE(

MAX(Fct_Table[Category]),

TOPN(1, FILTER(

SUMMARIZE ( Fct_Table, Fct_Table[Gender], Fct_Table[Category],

"@HC", [Change]), Fct_Table[Gender] = "Female"),

[Change])

)

however I wanted to retrive MIN value. Changed MAX(Fct_Table[Category]) to MIN(Fct_Table[Category]), tried to make TOPN1 DESC/ASC nothing works

Any suggestions?

[krohitrao]

1. Create a measure for getting max of the change:
    MaxChange = CALCULATE(MAX(Fct_Table[Change]), ALL(Fact_Table))
2. Create another measure to get the category with max change:
    MaxCategory = CALCULATE(MAX(Fct_Table[Category]), FILTER(Fact_Table, Fct_Table[Change] = [MaxChange]))

Same goes with finding minimum category, create another set of measures and replace MAX with MIN

[Pbiuserr]

Yes but the table in this scenario must consist Fct_Table[Gender] and Fct_Table[Category] and its need to be sorted by [Change] measure. Can you help me build such measure?

Edit: Change is not a Fct_Table[Change], its a measure

[rohit_singh]

Hello @Pbiuserr ,

Could you please share sample data in text format along with expected output if possible?

Kind regards,

Rohit

[Pbiuserr]

Hey,
Sory, data is confidential unfortunately

Expected result is:

You have a table like (I've made it inside the measure)

Gender	Category	Change
Male	Category2	10%
Female	Category1	2%
Female	Category5	3%
Male	Category3	7%

from that I want retrive name of Category1 as its MIN [Change] (2%). The measure I've put will retrived Category2 as its MAX [Change] (10%). I can make it for MAX but can't for MIN and seek some help

[rohit_singh]

Hi @Pbiuserr ,

Please try creating the following measures :

Min Change =

var _min =

CALCULATE(

MIN(Gender[Change]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female"

)

)

var _category =

CALCULATE(

VALUES(Gender[Category]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _min

)

)

RETURN

_category

Max Change =

var _max =

CALCULATE(

MAX(Gender[Change]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female"

)

)

var _category =

CALCULATE(

VALUES(Gender[Category]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _max

)

)

RETURN

_category

 
Final output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Pbiuserr]

Hello, 
[Change] Is a measure, not a column and MIN needs a column reference. How to overcome it?

I have two columns:

Fct_Table [Category]

Fct_Table [Gender]

and one measure

[Change]

[rohit_singh]

Hello @Pbiuserr ,

In that case you will have to use the MAXX and MINX functions to calculate max and min values of change. 

I have assumed that you have a single measure named "__Change" that computes the change. 

__MaxChange =

var _max =

MAXX(

FILTER(ALLSELECTED(Gender),

Gender[Gender] = "Female"),

[__Change]

)

var _category =

CALCULATE(

VALUES(Gender[Category]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _max

)

)

RETURN

_category

__MinChange =

var _min =

MINX(

FILTER(ALLSELECTED(Gender),

Gender[Gender] = "Female"),

[__Change]

)

var _category =

CALCULATE(

VALUES(Gender[Category]),

FILTER(ALLSELECTED(Gender), Gender[Gender] = "Female" && Gender[Change] = _min

)

)

RETURN

_category

This gives you the following result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Pbiuserr]

Hello,

There is Gender[Change], which in fact I can't put as a column, because its measure 😔
when I put [Change] instead of Gender[Change] I got an error that "table of multiple values was supplied". I think due to VALUES(FctTable[Category]) ?

[Tahreem24 · Accepted solution]

@Pbiuserr TRy this Measure

MINX ( SUMMARIZE ( Table, Table[GroupBy] , "Measure1",[YourMeasure] ), [Measure1])

Else refer this accepted solution:

https://community.powerbi.com/t5/Desktop/Return-a-category-name-associated-with-a-minimum-value/m-p/...

[Pbiuserr]

Hey,

What you've wrote gives me MIN value of [Change]. What I'd like to get it name of category associated with that minimum value. That's the part I can't get

so for my table is Category 1 as it has MIN value of [Change] - 2%

[Tahreem24]

@Pbiuserr Try this measure:

Measure = MINX(SUMMARIZE(ALL('Table (2)'),'Table (2)'[Category],"Change_",MIN('Table (2)'[Change])),'Table (2)'[Category])

[Pbiuserr]

I have an error that MIN function accepts only column references in the Argument1
Could it be potentially because [Change] is measure, not a calculated column?

MINX(SUMMARIZE(ALL( Fct_Table), Fct_Table[Gender], Fct_Table[Category], "Change_",MIN([Change])), Fct_Table[Category])

i'd also want to filter it to be for "female" gender only, that why I wanted to make my working measure for MAX just amend somehow to working on MIN