Concatenate Max Values

[Anonymous]

Hi everyone, 

I have a table with Year, the provinces of the Netherlands and the substance in water. I want to show the province(s) with the highest amount of a substance per year. 

This measures works: 

MaxAmountOneProvince = 

CALCULATE(

    MAX('Province'[Province]),

               FILTER('Water',

                   'Water'[Hoeveelheid (mg/l)] = MAX('Water'[AmountSubstance])),

                   ALLEXCEPT(

                   'Water',

                   'Water'[Year],

                   'Water'[Substance]))

However, sometimes there are two or more provinces with the same max amount of substance. I tried several things, including the following measure:

MaxAmountSeveralProvinces = 

VAR MaxConcentration = MAX('Water'[AmountSubstance])

RETURN

    CONCATENATEX(

        FILTER(

            'Province',

            CALCULATE(

                MAX('Water'[AmountSubstance]),

                ALLEXCEPT(

                    'Water',

                    'Water'[Year],

                    'Water'[Substance],

                    'Provincies'

                )

            ) = MaxConcentration

        ),

        'Province'[Province],

        ", "

    )

The results are vague and incorrect (see images). I hope someone can figure out what's wrong. 

[amitchandak · Accepted solution]

@Anonymous , prefer topn

concatenatex(Topn(1, allselected('Province'[Province]) , [Your Meausre], desc) , [Province])

You can also consider index function

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448