topic Re: Textsplit command in Power BI in DAX Commands and Tips

Textsplit command in Power BI

Anonymous — Tue, 15 Aug 2023 09:36:10 GMT

Hi,

I have a list of adresses with the age of the habitants of each adres. I'm trying to create a measure in Power BI to show the average age of each adress. However, as seen below the age is divided by a comma in a single cell. I don't want to split the cell into multiple rows or colums as some adresses consist of hundreds of habitants and i also have already 70K+ rows.

Age	Average age
29	29
22,22,22	22
22,21,21	21,33333333
22,20	21
28	28
0	0
0	0
32,31,24,23	27,5
46,45,12,11,6	24

The formula i used in Excell is the following:

=AVERAGE(VALUE(TEXTSPLIT('Age',",")))

I would like to create a measure in Power BI similar to the formula in Excell, which has the output of Average age. What would be a similair formula to achieve the same result?

Cheers

Re: Textsplit command in Power BI

tamerj1 — Tue, 15 Aug 2023 10:25:12 GMT

Hi Anonymous

please try

Average Age =
VAR String = 'Table'[Age]
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T =
GENERATESERIES ( 1, Length, 1 )
RETURN
AVERAGEX ( SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) ), [@Item] )

Re: Textsplit command in Power BI

Anonymous — Wed, 06 Sep 2023 09:44:10 GMT

Hello tamerj1,

Thank you for your quick response, i occured one problem when putting in the formula. Which is that PowerBI does not recognize my 'Table'[Age] when i try to type it as VAR String =. Currently it is marked as text in PowerBI. What can i do to solve this.

Re: Textsplit command in Power BI

tamerj1 — Wed, 06 Sep 2023 10:12:37 GMT

Anonymous

The code was intended for a calculated column. If you wish to create a measure you need to use aggregation function like MAX, MIN or SELECTEDVALUE