Forum Discussion
Helper IVdisplaying a description in a measure based upon another variable
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
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.
4 Replies
- Deku
Super User
Flavour with highest amount=
MAXX(
Topn(
1,
Table,
Table[amount],
Desc
),
Table[flavour]
)
v-pnaroju-msftCommunity Support
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.
- 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
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.
