cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
codruta1902
Regular Visitor

Calculate min value of a column then select to return corresponding text value from another column.

Hi,

 

Can anyone help me with a DAX formula that would return city name (need to use "card visual") by calculating the minimum value of a numeric column. And in case 2 cities have same value to select the first value. In below example for the Card Visual I wanted to have the city "San Roque". 

 

pbicc.png

 

 

Thank you!

 

1 ACCEPTED SOLUTION
jppv20
Solution Sage
Solution Sage

Hi @codruta1902 ,

 

Try this measure:

MinQuantity = CALCULATE(MIN('Table'[city]),TOPN(1,'Table','Table'[TotalPollutantQuantity],ASC))
 
Result:
jppv20_0-1634216536704.png

 

Jori

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

View solution in original post

8 REPLIES 8
jppv20
Solution Sage
Solution Sage

Hi @codruta1902 ,

 

I'm sorry but I it is not really clear to me what is not working with the TopN. Also, when I copy your formula I receive an error. Can you explain a bit more?

Hi,

I shared a printscreen below. Something is not calculating correct -> try to replace for the table where is country/city/totalpollutant instead of "Don't Summarize" with min of TotalPollutantQuantity.  Maybe issue comes because are different values for different dates? 

And you can download pbix file from here https://drive.google.com/drive/folders/1RmitcIJtDbvruahf7UdQKOxaS9ysMMQ-?usp=sharing 

pbicccc.png

Sorry, formula I sent had mistake, was missin these '' for table.

MinQuantity2 =
VAR MinPollutant =
MINX('Table','Table'[TotalPollutantQuantity])
RETURN
CALCULATE (
SELECTEDVALUE ('Table'[city]),
'Table'[TotalPollutantQuantity] = MinPollutant,
KEEPFILTERS(VALUES('Table'[city]))
)


Thank you very much!  🙂

Hi @codruta1902 ,

 

If you change "Top 5" to "Bottom 5" for the chart you will get this result:

jppv20_0-1634282359168.png

 

For the card, what is the desired output there?

Hi,

 

Thank you very much for this. I really appreciate it!

I just wanted that all 3 visuals (card, table, and chart (with top5) would show the same result (the correct one), to display the cleansest city (min pollutant).


_For the chart to show same result as the card have to use "Min" of TotalPollutantQuantity.
_For the table to show same results I have to use "Don't Summarize" TotalPollutantQuantity.

_But if I use a Matrix Table as there "Don't Summarize" is not available have to use also  "Min" of TotalPollutantQuantity.

Your first answer was perfect and will accept it as solution, the chart and table were showing different results and I was not understanding what is happening, until I chaged for the table to have from Min to "Don't Summarize".

Hi again,

 

Just in case you have time, if I want to create a card with average value for same dataset, replacing the "Min" with "Average" in your formula, I get the error on the card "Average cannot work with values of type String", what would be the workaround for the measure?

@codruta1902 

For calculating average value you can use AVERAGE(Table'[TotalPollutantQuantity])

jppv20
Solution Sage
Solution Sage

Hi @codruta1902 ,

 

Try this measure:

MinQuantity = CALCULATE(MIN('Table'[city]),TOPN(1,'Table','Table'[TotalPollutantQuantity],ASC))
 
Result:
jppv20_0-1634216536704.png

 

Jori

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

Hi,
First I want to thank you so much for working on this. I am a beginner.
Something is strange with TopN because it looks is not cheking complete dataset, even if I use it on a chart and apply TopN on "page filter" it gives different result if I select TopN 1 item or 5 items. 

Below formula returns "blank" and works only if I filter some of the countries.
What should be added to select first value for the "card visual", in case 2 cities have same value?



MinQuantity =
VAR MinPollutant =
MINX(Table,Table[TotalPollutantQuantity])
RETURN
CALCULATE (
SELECTEDVALUE ( Table[city]),
Table[TotalPollutantQuantity] = MinPollutant,
KEEPFILTERS(VALUES(Table[city]))
)

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!