How to get max Value in PowerBI for a measure for each category

0
votes

i am making a powerbi report which should give me the category having maximum value of defect (not total)

This is my dataset PowerBI Dataset

I am trying to use the following DAX formula

NO 1 Defect = LOOKUPVALUE('Camera Data'[Attribute],'Camera Data'[Value],MAX('Camera Data'[Value]))

And i am using a card to display the category of max value. This works fine till the time i have a single max value. the moment it becomes more than 1 then it is giving error

Multiple values supplied where only 1 value was expected

All i want is only the first of that max value category should be displayed on the card. How can i do it?

PS: to extract data from the image you can use this site https://extracttable.com/ this would work for free and 1 image will be easily converted to dataset.

1
powerbidaxpowerbi-desktoppowerbi-datasource

1 Answers

0
votes

A simple way to get the Attribute correpsonding to the maximum value is to use CALCULATE to apply a filter context on the rows with the maximum value and then to use MAX() or MIN() to get only one value in case of ties

NO 1 Defect = 
VAR MaxValue = MAX( 'Camera Data'[Value] )
RETURN
CALCULATE(
    MAX('Camera Data'[Attribute]),
    'Camera Data'[Value] = MaxValue
)

If all Attributes are to be displayed in case of ties, then the CONCATENATEX() function can be used

NO 1 Defect List = 
VAR MaxValue = MAX( 'Camera Data'[Value] )
RETURN
CALCULATE(
    CONCATENATEX('Camera Data', 'Camera Data'[Attribute], ", " ),
    'Camera Data'[Value] = MaxValue
)

In case the "Total Count" rows that appear in your image are actual rows to be excluded, an alternative definition for MaxValue variable should be used

VAR MaxValue = CALCULATE(
                    MAX( 'Camera Data'[Value] ), 
                    'Camera Data'[Attribute] <> "Total Count"
               )