Getting blank values instead of actual MIN or MAX in a category

[Zyg_D]

This is the data: 

I want to create a table based on Table1 which would contain every category just once and MIN of the category from one column and MAX of the category from another column. The tricky thing is that if a blank value exists in the category, I want to return the blank value. This is the desired result: 

How to achieve it?

[Zyg_D]

Sorry @v-eqin-msft  I had to write another answer, because I found an error in yours. 

The following line in your code does not effectively check if there are blank values in the filtered column (if in the filtered column there are no blank values, but several different numbers, the SELECTEDVALUE returns blank too, which is why in my case it is not a good choice):

ISBLANK ( SELECTEDVALUE ( 'Table1'[Value] ) )

 I have changed the line with this:

COUNTBLANK(Table1[Value])

[Zyg_D · Accepted solution]

Based on the answer from @v-eqin-msft  I was able to create what I needed. 

In the Table1 I added 2 columns:

minValue = 
IF (
    CALCULATE (
        COUNTBLANK(Table1[Value]),
        ALLEXCEPT ( Table1, Table1[Category] )
    ),
    BLANK(),
    CALCULATE ( 
        MIN ( Table1[Value] ), 
        ALLEXCEPT ( Table1, Table1[Category] ) 
    )
)

maxValue2 = 
IF (
    CALCULATE (
        COUNTBLANK(Table1[Value2]),
        ALLEXCEPT ( Table1, Table1[Category] )
    ),
    BLANK(),
    CALCULATE ( 
        MAX ( Table1[Value2] ), 
        ALLEXCEPT ( Table1, Table1[Category] ) 
    )
)

And then created a new table:

ResultTable = 
DISTINCT (
    SELECTCOLUMNS (
        'Table1',
        "Cat", [Category],
        "Val", [minValue],
        "Val2", [maxValue2]
    )
)

[v-eqin-msft]

Hi @Zyg_D ,

According to my understanding, you want to create a table with three columns: distinct Category, Min Value and Max Value2 , right?

You could use the following formula:

//Create columns

minValue =
IF (
    CALCULATE (
        ISBLANK ( SELECTEDVALUE ( 'Table1'[Value] ) ),
        ALLEXCEPT ( Table1, Table1[Category] )
    ),
    BLANK (),
    CALCULATE ( MIN ( 'Table1'[Value] ), ALLEXCEPT ( 'Table1', Table1[Category] ) )
)

maxValue2 =
IF (
    CALCULATE (
        ISBLANK ( SELECTEDVALUE ( 'Table1'[Value2] ) ),
        ALLEXCEPT ( Table1, Table1[Category] )
    ),
    BLANK (),
    CALCULATE ( MAX ( 'Table1'[Value2] ), ALLEXCEPT ( 'Table1', Table1[Category] ) )
)

//Create table

Table 2 =
DISTINCT (
    SELECTCOLUMNS (
        'Table1',
        "Cat", [Category],
        "Val", [minValue],
        "Val2", [maxValue2]
    )
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Zyg_D]

Thank you, @v-eqin-msft  . I was hoping a shorter solution exists, but your answer does exactly what I described, so thanks! 

[Zyg_D]

Any more ideas?

[Sujit_Thakur]

Do you want to ignore blank or count it as minimun of all??

[Zyg_D]

---

@Sujit_Thakur wrote:

Do you want to ignore blank or count it as minimun of all??

---

I don't want to ignore blank values. if they are there, they must appear both, as minimum and as maximum. 

[amitchandak]

@Zyg_D , try

maxx(filter(table,not(isblank(table[value]))),table[Value])
minx(filter(table,not(isblank(table[value]))),table[Value])

maxx(filter(table,not(isblank(table[value2]))),table[Value2])
minx(filter(table,not(isblank(table[value2]))),table[Value2])

[Zyg_D]

---

@amitchandak wrote:

@Zyg_D, try

maxx(filter(table,not(isblank(table[value]))),table[Value])
minx(filter(table,not(isblank(table[value]))),table[Value])

maxx(filter(table,not(isblank(table[value2]))),table[Value2])
minx(filter(table,not(isblank(table[value2]))),table[Value2])

---

Please elaborate more on your answer. As it is, it is impossible to tell how I should get my desired table. 

Error: "The expression specified in the query is not a valid table expression."