cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvilten
Helper II
Helper II

Creating new column with concatenateX

Hello All,

I have been going in circles with this and finally decided to reach out. I have two tables and want to create a new table (I think open to Ideas) Applications is the first table System Dependencies is the second and last is the goal. The SystemDependenciesId column in the Applications table came from an extracted list using | as a deliminater. I tried using an expand to rows instead but was still unable to get the concantenated list column that I want.

Applications 
IDTitleSystemDependenciesId
5956A22|161|164|55|370
5957B 
5958C6|52|369
5959D22|161|164

 

Dependencies
IDTitle
1-
6System 6
22System 7
52System 8
55System 9
161System 10
164System 11
369System 12
370System 13

 

Goal   
IDTitleSystemDependenciesIdSysDepList
5956A22|161|164|55|370System 6, System 8, System 10, System 11, System 13
5957B  
5958C6|52|369System 6, System 8, System 12
5959D22|161|164System 6, System 10, System 11

 

TIA

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@bvilten add a new column in your application table using following DAX expression;

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", [@ID] 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@bvilten well done. ofcourse it is good to understand the expression and can become useful in the future. Glad you have a solution in place. Cheers!! Don't hesitate to subscribe to my YouTube channel where I post interesting videos on Power BI. Good luck!

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@bvilten there is somethingng else going on with the pbix file you are working with. it worked fine at my end when I used the sample data you provided. Share your pbix file using 1/g drive, remove sensitive information before sharing.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes you are absolutely correct. As I was trying to understand your solution rather than just cut and paste I found TreatAs is for use where no relationship exists. In my attempts to solve this I had created an intremediary table that related to both systemdependencies and application. Once I removed this both the measure and the column solution worked great. Thank you both.

bvilten
Helper II
Helper II

Changing the Dependency[ID] to text cleared the errors I was getting from both the measure and the add column DAX. However both now return blanks for data, excepting that the measure does return the correct number of commas per items in Application[SystemDependenciesId]

parry2k
Super User
Super User

@bvilten or change the expression as below:

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", IFERROR ( CONVERT ( [@ID], INTEGER ), BLANK () ) 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@bvilten what is ID column type in dependency table, make sure it is text.

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





smpa01
Super User
Super User

@bvilten  you can use a single measure like this to achieve the result

Measure =
CONCATENATEX (
    ADDCOLUMNS (
        FILTER (
            GENERATE (
                Applications,
                ADDCOLUMNS (
                    GENERATESERIES ( 1, PATHLENGTH ( Applications[SystemDependenciesId] ) ),
                    "new", PATHITEM ( [SystemDependenciesId], [Value], TEXT )
                )
            ),
            [new] <> BLANK ()
        ),
        "_title",
            CALCULATE (
                MAX ( 'Dependencies'[Title] ),
                VAR _x = [new] RETURN TREATAS ( { _x }, 'Dependencies'[ID] )
            )
    ),
    [_title],
    ",",
    [_title], DESC
)

 

 

smpa01_0-1642432376195.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


HotChilli
Super User
Super User

Is there something mysterious going on with the SysDepList column because a lot of them don't match up with the id from dependencies table? e.g. system 6 is not 22 in the firstline of the goal table.

--

Personally, I would use Dependencies as a dimension table, Applications as the fact table.  And I would split the SystemDependenciesId column 'by delimiter' to Rows.

After this, f you want to create measures to display the columns (as in the  Goal table) then concatenatex is the way to go.

 

Let me know what you think

Sorry about that, mysterious only in that I hand typed all the example tables Apearently without looking 😉

parry2k
Super User
Super User

@bvilten and here is the output 

parry2k_0-1642430575179.png

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@bvilten add a new column in your application table using following DAX expression;

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", [@ID] 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thank you all, Since I was looking for a new column I started with this solution. I keep getting this error

bvilten_0-1642441302674.png

Thinking that it didn't like the empty or blanks in the  SystemDependenciesId column I tried wrapping the whole thing in an IF( isblank(systemDependenciesId ), blank(), "Above solution") but that didn't work I also tried check for pathlength <= 0 and returning 1 that did not work either and got the same error. Since it caliming a problem with [static column] I wonder if the real problem is in the PATHITEM line, but either way not sure how to fix it.

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!