topic Re: Calculated table of MIN value in DAX Commands and Tips

Calculated table of MIN value

Atiroocky — Thu, 27 Apr 2023 07:17:54 GMT

Hello,

To get my data easier to plot on scatter visual, I have unpivoted my data source.

Original

		z1	z2	z3
x1	y1	V1	V4	V7
x2	y2	V2	V5	V8
x3	y3	V3	V6	V9

Unpivoted

X	Y	Z	Value
x1	y1	z1	V1
x2	y2	z1	V2
x3	y3	z1	V3
x1	y1	z2	V4
x2	y2	z2	V5
x3	y3	z2	V6
x1	y1	z3	V7
x2	y2	z3	V8
x3	y3	z3	V9

I use as scatter plot in which I want to visualize the min value of each couple of (xi ; yi).

I works, but values are overlapping. The top of the pile is the "min value" as well. It’s ok but I can see the color of the other values behind.

It’s a bit unsightly, so I would like to calculate a table which will only contain the min value, and directly scatter plot from it.

I would like to get a table like this from the unpivoted table

X	Y	Calculated min value	Return z
x1	y1	min Value	z ?
x2	y2	min Value	z ?
x3	y3	min value	z ?

How can I do it with DAX ?

Thanx

Re: Calculated table of MIN value

johnt75 — Thu, 27 Apr 2023 08:54:44 GMT

Firstly you need to make sure that your underlying data table has at least one column which can uniquely identify a row. You can use Power Query to add an index column. You then need to go into the Model view, select the data table and mark the index column as the key column.

You can then create a summary table like

Summary Table = INDEX( 1, 'Table', ORDERBY( 'Table'[Value], DESC, 'Table'[Index], DESC), PARTITIONBY( 'Table'[X], 'Table'[Y]) )

Re: Calculated table of MIN value

Atiroocky — Fri, 28 Apr 2023 09:53:19 GMT

Thanks for your reply.

I’ve done exactly what you mentionned :

- create index column with PowerQuery

- set index column as key column in data Model tab

- rewrite your code to match my table/column names.

The result table has the good shape. I mean, I only have single couple of (xi,yi).
But the value returned is NOT the MIN of (xi, yi) among z1, z2 and z3

EDIT :

I managed to get what I wanted thanks to EARLIER function.

Min_Value = CALCULATE( MIN(Table1[Value]), FILTER(Table1,((Table1[X]=EARLIER(Table1[X]))*(Table1[Y]=EARLIER(Table1[Y]])) ))

Then I add a new "true/false" column which returns "true" if Value = Min_Value

X	Y	Z	Value	Min_Value	Test
x1	y1	z1	V1	V4	false
x2	y2	z1	V2	V2	true
x3	y3	z1	V3	V9	false
x1	y1	z2	V4	V4	true
x2	y2	z2	V5	V2	false
x3	y3	z2	V6	V9	false
x1	y1	z3	V7	V4	false
x2	y2	z3	V8	V2	false
x3	y3	z3	V9	V9	true

Then I created a new table with filter of "true" values

X	Y	Z	Min_Value
x2	y2	z1	V2
x1	y1	z2	V4
x3	y3	z3	V9

If you have an easiest way to procede, I catch !

Re: Calculated table of MIN value

Ajendra — Fri, 28 Apr 2023 21:33:50 GMT

Hey,

I hope you are doing well!

Try this one!

Used data as shown below screen shot: