Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Textsplit command in Power BI

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 di...
  • tamerj1's avatar
    tamerj1
    3 years ago

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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • tamerj1's avatar
      tamerj1
      Community Champion
      2 years ago

      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