Excel Formula to PowerBI

[Anonymous]

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

[BhaveshPatel]

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.

[LaurentCouartou]

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]

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.

[v-sihou-msft]

@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]

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]

@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,

[Anonymous]

@v-sihou-msft

If i try to measure the if clause with istext(true) then blank() everything is dropped:

i guess the problem is, there are dots between the numbers i was out for (example 23.02.02.2003 or 08.03.01.1). Those stay without the dots when i switch to whole number but i lose the first 0 like in my second example.

Now i was trying to do so:

but now i get the error " if result is a variant-typ, it can not be used for defined calculated columns".

if(isnumber(left([Hilfsmittelnummer];2));[Hilfsmittelnummer];if(istext([Hilfsmittelnummer]);blank()))

[Anonymous]

@v-sihou-msft

i changed my way of solving the problem and took this formula. looking for the dot on third point and so on... 😄 works better that way since i can't solve the other formula problems.

if(mid([Hilfsmittelnummer];3;1)=".";left([Hilfsmittelnummer];2);blank())

 thank you all for the help!

[LaurentCouartou]

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
)