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)*1);ISNUMBER(INT(MID([Hilfsmittelnummer];4;1))));LEFT([Hilfsmittelnummer];2)*1;"")

 

It said the "and" clause has to many arguments (3 arguments instead of 2). Is there another way to complete my formula, I didn't know about, that is usable via PowerBi?

Basically, in Excel it does work great but since I work with CSV Data, forumla integration doesn't make much sense at all.

 

I am grateful for feedback and any help.

 

Sincerly Sam

Need Help

9 Replies

  • 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
) 

         

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Posting a data and expected output would help you to provide a suggestion. As your xcel formula is nested within other formula, I would urge you to post a sample data.