Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | ||
ID | Title | SystemDependenciesId |
5956 | A | 22|161|164|55|370 |
5957 | B | |
5958 | C | 6|52|369 |
5959 | D | 22|161|164 |
Dependencies | |
ID | Title |
1 | - |
6 | System 6 |
22 | System 7 |
52 | System 8 |
55 | System 9 |
161 | System 10 |
164 | System 11 |
369 | System 12 |
370 | System 13 |
Goal | |||
ID | Title | SystemDependenciesId | SysDepList |
5956 | A | 22|161|164|55|370 | System 6, System 8, System 10, System 11, System 13 |
5957 | B | ||
5958 | C | 6|52|369 | System 6, System 8, System 12 |
5959 | D | 22|161|164 | System 6, System 10, System 11 |
TIA
Solved! Go to Solution.
@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], "," )
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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!
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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]
@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], "," )
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@bvilten what is ID column type in dependency table, make sure it is text.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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
)
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 😉
@bvilten and here is the output
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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], "," )
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |