Concatenate Max Values

Question

Hi everyone,&nbsp;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.&nbsp;This measures works:&nbsp;MaxAmountOneProvince =&nbsp;CALCULATE(&nbsp; &nbsp; MAX('Province'[Province]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER('Water',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Water'[Hoeveelheid (mg/l)] = MAX('Water'[AmountSubstance])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALLEXCEPT(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Water', &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Water'[Year],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'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 =&nbsp;VAR MaxConcentration = MAX('Water'[AmountSubstance])RETURN&nbsp; &nbsp; CONCATENATEX(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Province',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX('Water'[AmountSubstance]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLEXCEPT(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Water', &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Water'[Year],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Water'[Substance],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Provincies'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) = MaxConcentration&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'Province'[Province],&nbsp; &nbsp; &nbsp; &nbsp; ", "&nbsp; &nbsp; )The results are vague and incorrect (see images). I hope someone can figure out what's wrong.&nbsp;&nbsp;

amitchandak · Accepted Answer

Anonymous , prefer topn 
concatenatex(Topn(1, allselected('Province'[Province]) , [Your Meausre], desc) , [Province])
&nbsp;
You can also consider index function
Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U
&nbsp;
TOPN: https://youtu.be/QIVEFp-QiOk TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448

Forum Discussion

Concatenate Max Values

1 Reply

Related content

concatenate values

Concatenate rows values

Find MAX Value within a column

Total Min/Max measure where and keep the value static

Concatenate lists ;