Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Greetings everyone!
I need help with the following moment:
There are 2 columns:
1) Say the first column is a name of product
2) Second column is a part that belongs to a certain product
A particular part can be assigned to any product:
What I want:
To create a measure, where:
- DAX checks IF:
- part belongs to only one project - it returns the name of the project (i.e. 1st column's value)
- part belongs to a couple of projects - it returns concatenated string where project names are concatenated via comma
The result will be in the third column. Thank you!
Solved! Go to Solution.
@Anonymous
Please try
=
VAR CurrentProject =
SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
RETURN
IF (
COUNTROWS ( CurrentPartTable ) = 1,
CurrentProject,
CONCATENATEX ( CurrentPartTable, TableName[Name], ", ", TableName[Name], ASC )
)
@Anonymous
Please try
=
VAR CurrentProject =
SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
RETURN
IF (
COUNTROWS ( CurrentPartTable ) = 1,
CurrentProject,
CONCATENATEX ( CurrentPartTable, TableName[Name], ", ", TableName[Name], ASC )
)
I am sorry, silly me. Your solution actually works, it was me who didn't make it work right. THANK YOU!
@Anonymous
Happy to hear that. Thank you for marking my reply as acceptable solution
Would You explain your solution to me so I could get it right, please? Like I am 5 yo would be more preferrable...
Hi @Anonymous
Here you go
=
VAR CurrentProject =
-- This retreives the project name in the current filter context.
SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
-- This removes the all filters from the table except the one coming from Part # column
-- When iteracted with the current filter context it will leave a table composed of
-- all the rows that contains the Part # available in the current filter context.
CALCULATETABLE (
TableName,
ALLEXCEPT ( TableName, TableName[Part #] )
)
RETURN
IF (
COUNTROWS ( CurrentPartTable ) = 1,
-- If this table contains only one row that means that the Part # available in the
-- current filter context is not repeated (in other words it is associated with only one project name.
CurrentProject,
-- Then return the project name avialable in the current filter context. ( this is only to avoid displying the delimiter)
CONCATENATEX (
CurrentPartTable,
TableName[Name],
", ",
TableName[Name], ASC
) -- Otherwise (meaning if more than one project is associated to this Part #), concatenate all the names ordered ascending
-- by project name seperated by ", "
)
It return the list of every occurence now, but that's a good way to start?
@Anonymous
Didn't know they are repeated. Please use
=
VAR CurrentProject =
SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
VAR ProjectNames =
DISTINCT ( SELECTCOLUMNS ( CurrentPartTable, "@Name", TableName[Name] ) )
RETURN
IF (
COUNTROWS ( ProjectNames ) = 1,
CurrentProject,
CONCATENATEX ( ProjectNames, [@Name], ", ", [@Name], ASC )
)
Hi @Anonymous
Do you want to create a measure or a calculated column? Can you share the same sample data as copy/paste?
but there are only 2 columns that are being shown on the screenshot
A measure would be more preferrable. I guess it's going to take a couple of variables to make it. Unfortunately, I can't send the data because I have no rights....
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |