Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ukihellsto
New Member

MIN or MAX from multiple MEASURES

Struggling to create a measure or calculated column which would result MIN or MAX value from several other measures or calculated columns.
Lets say that I have a table with x amount of rows and columns, then I've created 5 measures based on other information on the table and I'd like to have additional measure resulting MIN value from those 5 other measures, not to mention that I need exclude any blanks or zeroes from the comparison... Any easy way or anyway this could be achieved.
I could do the same also with calculated columns, but also this seems to be bit challenging, as I need to exlude all blanks or zeroes from equation, which makes bit more challenging.
Here's an example table

Column AColumn BColumn CColumn DColumn EColumn FMinimum
18,0006,000 9,0003,0003,000
33,0006,0005,0005,0005,0003,000
59,000 3,0002,0009,0002,000
65,0004,0003,000 9,0003,000
85,0003,0001,0004,0001,0001,000
9,7 1,000  8,0001,000
11,43,0003,0004,0003,0001,0001,000
13,15,000   10,0005,000
14,81,0006,0007,00010,0009,0001,000
16,54,0005,000 9,0004,0004,000
18,28,0009,0003,0004,0006,0003,000
19,9 8,0008,0009,0006,0006,000
21,68,0001,00010,0006,000 1,000
23,33,00010,000 10,0005,0003,000
1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@ukihellsto

 

According to your description, you want to get the max/min among multiple columns. Right?

 

In this scenario, we need to unpivot the Column B, C,..., F, have them group on Column A. Then calculate the min value within Column A group. Now you can summarize Column A and the calculation into a new table and build the relationship with source table. Please refer to steps below:

 

1. Click on Column A, select "Unpivot Other Columns"

 

123.PNG

 

234.PNG

 

2. Then filter the empty rows.

 

345.PNG

 

456.PNG

 

3. Create the Min Value measure:

 

Min Value = CALCULATE(MIN('Table5 (2)'[Value]),ALLEXCEPT('Table5 (2)','Table5 (2)'[Column A]))

4. Create a calculated table:

 

 

Table 2 = SUMMARIZE('Table5 (2)','Table5 (2)'[Column A],"Min Value",[Min Value])

567.PNG

 

 

5. Build the relationship with source table source table.

 

789.PNG

 

89.PNG

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@ukihellsto

 

According to your description, you want to get the max/min among multiple columns. Right?

 

In this scenario, we need to unpivot the Column B, C,..., F, have them group on Column A. Then calculate the min value within Column A group. Now you can summarize Column A and the calculation into a new table and build the relationship with source table. Please refer to steps below:

 

1. Click on Column A, select "Unpivot Other Columns"

 

123.PNG

 

234.PNG

 

2. Then filter the empty rows.

 

345.PNG

 

456.PNG

 

3. Create the Min Value measure:

 

Min Value = CALCULATE(MIN('Table5 (2)'[Value]),ALLEXCEPT('Table5 (2)','Table5 (2)'[Column A]))

4. Create a calculated table:

 

 

Table 2 = SUMMARIZE('Table5 (2)','Table5 (2)'[Column A],"Min Value",[Min Value])

567.PNG

 

 

5. Build the relationship with source table source table.

 

789.PNG

 

89.PNG

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors