topic Re: undefined in DAX Commands and Tips

undefined

Karthikgayathri — Fri, 28 Jul 2023 07:50:27 GMT

rubayatyasmin

i need output as text without the bracket ex : Head Office

I used to write the code

Right ( dim( customer) , search("(" , dim( customer) 1, len ( dim( customer )+1)-1)

My output is (Head Office)

I need without bracket in dax query

Re: undefined

rubayatyasmin — Fri, 28 Jul 2023 08:00:19 GMT

Hi, Karthikgayathri

Go to power query and use Split by Delimiter. Set the delimiter to (, it will split it in a new column. Rename the column. this kind of changes fall under data transformation. No need to write DAX for it. Power Query has it's own functionality to handle these sort of stuff.

Refer to this document : https://learn.microsoft.com/en-us/power-query/split-columns-delimiter

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 08:11:59 GMT

Bro I need dax formula by using left ,right ,mid functions

Re: undefined

rubayatyasmin — Fri, 28 Jul 2023 08:17:33 GMT

Use MID func.

=MID([ColumnName], FIND("(", [ColumnName], 1, LEN([ColumnName]), 0) + 1,
FIND(")", [ColumnName], 1, LEN([ColumnName]), 0) - FIND("(", [ColumnName], 1, LEN([ColumnName]), 0) - 1)

replace col name with actual column name .

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 08:58:04 GMT

Instead of 0 only u used len function then again using 0 how u used 0 ?

Re: undefined

tamerj1 — Fri, 28 Jul 2023 09:05:27 GMT

Hi Karthikgayathri
Please try

Bill to Customer 2 = SUBSTITUTE ( SUBSTITUTE ( dimCustomer[Bill to Customer], ")", "" ), "(", "" )

Re: undefined

rubayatyasmin — Fri, 28 Jul 2023 09:10:32 GMT

Here is the explanation of the formula:

FIND("(", [ColumnName], 1, LEN([ColumnName]), 0) + 1 - It finds the first character after the opening bracket.
FIND(")", [ColumnName], 1, LEN([ColumnName]), 0) - FIND("(", [ColumnName], 1, LEN([ColumnName]), 0) - 1 - It calculates the number of characters to extract.
MID([ColumnName], ..., ...) - This function will then extract the required number of characters starting from the specified position.

Also, know the details of the Find function.

refer: https://learn.microsoft.com/en-us/dax/find-function-dax

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 09:11:53 GMT

for your query above image

output . I need only head office without bracket

Re: undefined

tamerj1 — Fri, 28 Jul 2023 09:15:45 GMT

Karthikgayathri
Sorry. I misunderstood the requirement. Please try

Bill to Customer 2 = VAR String = dimCustomer[Bill to Customer] VAR Items = SUBSTITUTE ( SUBSTITUTE ( String, "(", "|" ), ")", "|" ) RETURN PATHITEM ( Items, 2 )

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 09:40:57 GMT

In 2 nd image - see the input

1 st image I got the same output but instead of N/A showing blank .in output both N/A and head office need to display.

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 09:42:39 GMT

Bro for find function only 4 input but u extra added 0 also .find ("(", column name , 1 ( starting position), len ( not found value ))

Re: undefined

rubayatyasmin — Fri, 28 Jul 2023 09:59:45 GMT

0 - not found value,

try using this code without LEN func and not_found_value param.

note: the following code assumes you have brackets in all the rows.

MID([ColumnName],
FIND("(", [ColumnName]) + 1,
FIND(")", [ColumnName]) - FIND("(", [ColumnName]) - 1)

MID([ColumnName], start_position, num_characters): MID function returns a string of characters from the middle of a text string, given a starting position and length. Here, the starting position and length are determined by the other two parts of the expression.

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 10:52:07 GMT

tamerj1 plz reply

Re: undefined

tamerj1 — Fri, 28 Jul 2023 13:00:17 GMT

Karthikgayathri

Bill to Customer 2 = VAR String = dimCustomer[Bill to Customer] VAR Items = SUBSTITUTE ( SUBSTITUTE ( String, "(", "|" ), ")", "|" ) RETURN COALESCE ( PATHITEM ( Items, 2 ), "N/A" )

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 13:59:21 GMT

Still N/A not displayed

Re: undefined

tamerj1 — Fri, 28 Jul 2023 14:52:14 GMT

Karthikgayathri

Try

Bill to Customer 2 = VAR String = dimCustomer[Bill to Customer] VAR Items = SUBSTITUTE ( SUBSTITUTE ( String, "(", "|" ), ")", "|" ) VAR Result = PATHITEM ( Items, 2 ) RETURN IF ( Result = BLANK ( ), "N/A", Result )

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 16:01:18 GMT

ok I will try your code . Above image see my code and my output. in output head office showing but in N/A only showing A alone wht is the issue in my code plz tell .

Re: undefined

Karthikgayathri — Fri, 28 Jul 2023 16:02:48 GMT

in my above code wht is the error bro . output showing head office crtly but N/A only showing A alone not showing N/A any error in my code plz tell

Re: undefined

tamerj1 — Fri, 28 Jul 2023 19:56:44 GMT

Karthikgayathri

This is not my code.

Re: undefined

Karthikgayathri — Sun, 30 Jul 2023 08:15:55 GMT

rubayatyasmin

@I have one doubt in Power bi service. In dashboard tile refresh failed.wht are the steps u follow ( one intervention questio