Forum Discussion

bvilten's avatar
bvilten
Icon for Helper III rankHelper III
4 years ago
Solved

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

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    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  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.

12 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

    • bvilten's avatar
      bvilten
      Icon for Helper III rankHelper III
      4 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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  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.

    • bvilten's avatar
      bvilten
      Icon for Helper III rankHelper III
      4 years ago

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

      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.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    bvilten and here is the output 

     

     

    Follow us on LinkedIn and  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.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
)

     

     

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

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

    Follow us on LinkedIn and  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.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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  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.

  • bvilten's avatar
    bvilten
    Icon for Helper III rankHelper III
    4 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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.

    • bvilten's avatar
      bvilten
      Icon for Helper III rankHelper III
      4 years ago

      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.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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  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.