Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Excel Formula to PowerBI

Hello Dears,   a short question: I was trying to use the following formula but PowerBI doesn't accept it:   Column = IF(AND(LEN(TRIM([Hilfsmittelnummer]))>=7;ISNUMBER(LEFT([Hilfsmittelnummer];2)...
Need Help
LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
9 years ago

The AND and OR functions in DAX only accept two arguments.

 

Complex expressions may require some ugly nesting AND(AND(OR(..)).

 

Fortunately, DAX has boolean operators, so you can write

expression1 && expression2 && expression3 (logical AND)

expression1 || expression2 || expression3 (logical OR)

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Columns = IF(LEN(TRIM([Hilfsmittelnummer]))>6&&ISNUMBER(LEFT([Hilfsmittelnummer];2)*1)&&ISNUMBER(INT(MID([Hilfsmittelnummer];4;1)));LEFT([Hilfsmittelnummer];2)*1;"")

Hello Laurent,

 

I have changed the formula, but know I receive the message that the datatyp variant, can't be used for the definition of calculating columns. (sorry if some meanings sound strange or irregular, I am not a native speaker).

 

A short Examaple: bottom is the regular and below is the irregular stuff. The Target is to cut the regulars until the first 2 numbers and drop the irregulars.

 

 

 

 

 

 

 

  • LaurentCouartou's avatar
    LaurentCouartou
    Solution Supplier
    9 years ago

    In your expression, the then part yields a number while the else part yields a string. The data-type returned would then change depending on the context. This is not allowed in a calculated column.

     

    To achieve the expected results (show nothing if your initial condition is not met), you could replace "" with BLANK()

    IF( condition
      ; then
      ; BLANK()
)

    or simply use the IF function with two arguments.

    IF( condition
      ; then
) 

     

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

    As your error message said, your formula returns a dynamic data type. You can only keep one data type. Please replace your empty string ("") with either BLANK() or 0.

     

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hello Simon_Hou,

       

      I am still struggling with the formula. I was trying blank() and 0 (zero) but it still answers with a error message:

       

      The Value "EI" of typ "Text" can't be converted into a "Number".

      I guess, in view of that, that there needs to be further advancement to the formula for the values with Letters.

       

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        9 years ago

        Anonymous

         

        You may use the some funcions which can only apply on numeric field in your condition, please change the data type of that "EI" field into "Whole Number" or "Decimal Number" type.

         

        Regards,