displaying a description in a measure based upon another variable

[jerryr125]

Hi - 

I would like to create a measure that displays a description based upon the maximum amount of a variable.

As an example of values in a data table:

Flavor	Amount
Vanilla	20000
Chocolate	35000
Cookies and Cream	15000

I would like to display Flavor which has the maximum amount.

Therefore the value of the Measure would be "Chocolate" given that is the flavor with the maximum Amount (35000).

Any thoughts would be appreciated - Jerry

[v-pnaroju-msft]

Hi @jerryr125,

Thank you for your follow-up.
Kindly find below the revised DAX measure, which may help in resolving the issue:

LocationWithMostVanillaOrStrawberry =

VAR FilteredTable =

FILTER(

'Flavors-Sold-Data-Table',

'Flavors-Sold-Data-Table'[Flavor] IN { "Vanilla", "Strawberry" }

)

VAR LocationCounts =

ADDCOLUMNS(

SUMMARIZE(

FilteredTable,

'Flavors-Sold-Data-Table'[Location]

),

"FlavorCount",

CALCULATE(COUNTROWS(FilteredTable))

)

VAR TopLocation =

TOPN(

1,

LocationCounts,

[FlavorCount], DESC

)

RETURN

MAXX(TopLocation, 'Flavors-Sold-Data-Table'[Location])

If you find our response helpful, we kindly request you to mark it as the accepted solution and consider giving kudos. This will assist other community members who may have similar queries.

Thank you.

[jerryr125]

Hi again - I think I defined my requirements incorrectly.

Input:

Flavors-Sold-Data-Table

Location	Flavor
A	Vanilla
B	Strawberry
B	Vanilla
A	Strawberry
B	Strawberry
B	Vanilla
B	Chocolate
A	Vanilla
A	Chocolate
A	Chocolate

I would like show the location with the maximum count of  flavor if the flavor is either Vanilla or Strawberry.

The output in the Measure I am looking for would be "B" since B has the highest count of the flavor Vanilla and Strawberry (4).

Any thoughts?

[v-pnaroju-msft · Accepted solution]

Thank you, @Deku , for your response.

Hi @jerryr125,

We appreciate your query posted on the Microsoft Fabric Community Forum.

In addition to the response provided by @Deku , please find below the DAX measure which may help resolve the issue:

FlavorWithMaxAmount =

CALCULATE(

SELECTEDVALUE('Table'[Flavor]),

FILTER(

'Table',

'Table'[Amount] = MAX('Table'[Amount])

)

)

If you find our response helpful, we kindly request you to mark it as the accepted solution and consider giving kudos. This will assist other community members who may have similar queries.

Thank you.

[Deku]

Flavour with highest amount=

MAXX(

Topn(

1,

Table,

Table[amount],

Desc

),

Table[flavour]

)