Search code examples
if-statementpowerbimaxdaxmin

Switch true for min and max range in power bi desktop

I am trying to specify each dimension range (length, width and height) of values specify criteria based on the length, width and height.

In data table I have 3 columns are length, width,height based on the 3 columns I would like to generate status columns which is based on the below mentioned condition range.

Conditions Range

  1. Length, width and height is start from 1 to 300 then A1.

  2. Length, width and height is start from 301 to 650 then A2.

  3. Length, width and height is start from 651 to 900 then A3.

  4. Length, width and height is start from 901 to 1200 then A4

  5. Length, width and height is start from 1201 above then XXX

Data

LENGTH WIDTH HEIGHT DESIRED RESULT RANGE
NA NA NA NA
20000 5000 230 XX
400 300 140 A1
BLANKS
600 400 285 A2
600 400 285 A2
400 300 150 A1
280 230 170 A1
320 320 320 A1
320 320 320 A1
600 400 140 A1
400 300 140 A1
400 300 140 A1
370 320 340 A1
320 240 250 A1
300 200 90 A1
400 290 140 A1
600 400 285 A1

Table and result look like

enter image description here

Any suggestions please

Solution

  • Well, you may try the following dax measure, as it will assign all row to each category, and assign to Other if does not match any of the criteria, do take note due to your data contain NA, therefore all the value is in Text for that need to convert to number during calculation:

    Result = IF(Sheet1[LENGTH] = "NA" || Sheet1[WIDTH] = "NA" || Sheet1[HEIGHT] ="NA", "NA",
            IF(VALUE(Sheet1[LENGTH])>0 && VALUE(Sheet1[LENGTH]) <=300 && VALUE(Sheet1[WIDTH]) >0 && VALUE(Sheet1[WIDTH]) <=300 && VALUE(Sheet1[HEIGHT]) >0 && VALUE(Sheet1    [HEIGHT]) <=300, "A1",

                IF(VALUE(Sheet1[LENGTH])>300 && VALUE(Sheet1[LENGTH]) <=650 && VALUE(Sheet1[WIDTH]) >300 && VALUE(Sheet1[WIDTH]) <=650 && VALUE(Sheet1[HEIGHT]) >300 && VALUE    (Sheet1[HEIGHT]) <=650, "A2",

                IF(VALUE(Sheet1[LENGTH])>650 && VALUE(Sheet1[LENGTH]) <=900 && VALUE(Sheet1[WIDTH]) >650 && VALUE(Sheet1[WIDTH]) <=900 && VALUE(Sheet1[HEIGHT]) >650 && VALUE    (Sheet1[HEIGHT]) <=900, "A3",

                    IF(VALUE(Sheet1[LENGTH])>900 && VALUE(Sheet1[LENGTH]) <=1200 && VALUE(Sheet1[WIDTH]) >900 && VALUE(Sheet1[WIDTH]) <=1200 && VALUE(Sheet1[HEIGHT]) >900 &&    VALUE(Sheet1[HEIGHT]) <=1200, "A4",

                        IF(VALUE(Sheet1[LENGTH])>1200 && VALUE(Sheet1[WIDTH]) >1200  && VALUE(Sheet1[HEIGHT]) >1200 , "XXX",
                            "Others"))))))

    Result:

    enter image description here