topic How to find the minimum value within a column depending on the other columns in the Dataset in DAX Commands and Tips

How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Wed, 23 Mar 2022 21:42:22 GMT

So in the picture below, you can see 3 columns. Now I want to choose the lowest value in the Ticket_owner_avg so the first column should look like:

Account - Creation| VNordfors| 0.14

and so on with the lowest value in the Ticket_owner_avg column.

I have tried to make a formula but so far nothing is working. I have changed the formula to this:

Min_Measure = GROUPBY(data, data[Item], "Ticket_owner_average",MINX(CURRENTGROUP(),data[Ticket_owner_avg]))

I am getting this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Any help is appreciated! Thank you

Re: How to find the minimum value within a column depending on the other columns in the Dataset

VahidDM — Wed, 23 Mar 2022 23:45:13 GMT

Hi Maulik_Patel

Can you post sample data as text and expected output?

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: How to find the minimum value within a column depending on the other columns in the Dataset

tamerj1 — Thu, 24 Mar 2022 04:11:35 GMT

Hi Maulik_Patel
GROUP by returns a table not a scalar value. Anyway, if I correctly understand your requirement then you can use

Min_Measure = MINX ( CALCULATETABLE ( data, ALLEXCEPT ( Data, data[Item] ) ), data[Ticket_owner_avg] )

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Fri, 25 Mar 2022 19:51:13 GMT

So now, the output is as follows in the image below:

I want the ouput in the way below:

Account Creation VNordfors 0.14 0.14

Account Disable RSmith 0.7 0.7

and so on, I do not want the other rows to show up, I just want the minimum for each Item in the Item column.

Re: How to find the minimum value within a column depending on the other columns in the Dataset

tamerj1 — Sat, 26 Mar 2022 02:24:36 GMT

Maulik_Patel

Create new measure

Filter Measure =
[Ticket_owner_avg] - [Min_Measure]

place this measure in the filter pane and select "is" and insert the value "0"

you can also take out the Min_Measure from the table if you are not interested in showing it.

please let me know if you still have any doupt

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Sat, 26 Mar 2022 13:49:36 GMT

So I made the measure and inserted it in the Filters on this visual section and chnaged the filter values accordingly but it just removes all the data and leaves me with a blank page except for the column headers as shown in the picture above:

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Sat, 26 Mar 2022 13:49:39 GMT

Re: How to find the minimum value within a column depending on the other columns in the Dataset

tamerj1 — Sat, 26 Mar 2022 13:56:05 GMT

Maulik_Patel

Is there a chance that the only the rounding of the numbers are equal but in fact they are not?

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Sat, 26 Mar 2022 14:03:46 GMT

I feel like the measure has some calculation errors as when I add it as a column to the visual, this is what I get:

Yes that could be the case, that only the rounded numbers are equal and not the actual numbers

Re: How to find the minimum value within a column depending on the other columns in the Dataset

tamerj1 — Sat, 26 Mar 2022 14:04:05 GMT

Maulik_Patel

Did you select "is"? Can you sahre a screenshot?

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Sat, 26 Mar 2022 14:05:50 GMT

Here it is!

Re: How to find the minimum value within a column depending on the other columns in the Dataset

tamerj1 — Sat, 26 Mar 2022 14:09:02 GMT

Maulik_Patel

My mistake I forgot that Ticket_owner_avg is a column

Filter Measure =
SUM ( [Ticket_owner_avg] ) - [Min_Measure]

Re: How to find the minimum value within a column depending on the other columns in the Dataset

Maulik_Patel — Sat, 26 Mar 2022 14:11:11 GMT

All good man, it worked and Thank you for your patience and help! I really appreciate it!