How to split caclulated column into rows using DAX?

Hi , Anonymous 

Thank you for your quick response and you solve it by yourself, you are genius !

I’m sorry i am mistaken in dax to split the column,as searched and test in my side. It can be realized in Power Query and also in dax.

Here are the steps i hope it can be helpful:

Power Query:

(1)This is my test data:

(2)We can use the Text.Split() and the List.Distinct() function to get the list we need, you can put this M code in the “Advanced Editor” in the “Home” Tab in Power Query Editor.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRSk1NVUhTSFOK1QGJGAFFkpMVUlIUQOLJQACVMAZKJCYqgBFUyAQiBFEOFzWFiCYlJSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member ID" = _t, Leads = _t]),
    Custom1 = Table.TransformColumns(Source , {"Leads",(x)=>List.Distinct(Text.Split(x," "))    }),
    #"Expanded Leads" = Table.ExpandListColumn(Custom1, "Leads")
in
#"Expanded Leads"

(3)Then we can meet your need:

Dax in Power BI Desktop :

(1)This is my test data:

(2)We need to click “New Column” to create a calculated column:

Column = var _text = SUBSTITUTE([Leads] , " ","|")
return
PATHLENGTH(_text)

(3)Then we can click “New Table” and enter this and we can split the column:

Table 2 = var _max = MAX('Table'[Column])
var _index = GENERATESERIES(1,_max)
var _ct = CROSSJOIN( _index , 'Table')
var _ft = FILTER( _ct , [Value] <= [Column])
var _at = ADDCOLUMNS(_ft , "Leads_value" ,PATHITEM( SUBSTITUTE( [Leads] , " ","|") , [Value]) )
return
DISTINCT( SELECTCOLUMNS( _at , "Member ID" , [Member ID] , "Leads" , [Leads_value]))

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly