Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 A | Column B | Column C | Column D | Column E | Column F | Minimum |
1 | 8,000 | 6,000 | 9,000 | 3,000 | 3,000 | |
3 | 3,000 | 6,000 | 5,000 | 5,000 | 5,000 | 3,000 |
5 | 9,000 | 3,000 | 2,000 | 9,000 | 2,000 | |
6 | 5,000 | 4,000 | 3,000 | 9,000 | 3,000 | |
8 | 5,000 | 3,000 | 1,000 | 4,000 | 1,000 | 1,000 |
9,7 | 1,000 | 8,000 | 1,000 | |||
11,4 | 3,000 | 3,000 | 4,000 | 3,000 | 1,000 | 1,000 |
13,1 | 5,000 | 10,000 | 5,000 | |||
14,8 | 1,000 | 6,000 | 7,000 | 10,000 | 9,000 | 1,000 |
16,5 | 4,000 | 5,000 | 9,000 | 4,000 | 4,000 | |
18,2 | 8,000 | 9,000 | 3,000 | 4,000 | 6,000 | 3,000 |
19,9 | 8,000 | 8,000 | 9,000 | 6,000 | 6,000 | |
21,6 | 8,000 | 1,000 | 10,000 | 6,000 | 1,000 | |
23,3 | 3,000 | 10,000 | 10,000 | 5,000 | 3,000 |
Solved! Go to Solution.
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"
2. Then filter the empty rows.
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])
5. Build the relationship with source table source table.
Regards,
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"
2. Then filter the empty rows.
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])
5. Build the relationship with source table source table.
Regards,