Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
i have a table
Circuit_Name | CONTRACT_ACCOUNT_NUMBER | CommunityName |
ARDMORE 24 | 322382 | Ardmore |
ARDMORE 24 | 322385 | Ardmore |
ARDMORE 24 | 322391 | Ardmore |
ARDMORE 24 | 322395 | Ardmore |
ARDMORE 24 | 322400 | Ardmore |
ARDMORE 24 | 131599652 | Ardmore |
ARDMORE 24 | 131600759 | Ardmore |
ARDMORE 24 | 131601250 | Ardmore |
ARDMORE 24 | 131601585 | Ardmore |
ARDMORE 24 | 2000764 | Marietta |
ARDMORE 24 | 127716387 | Marietta |
ARDMORE 24 | 128010672 | Marietta |
ARDMORE 24 | 128226326 | Marietta |
ARDMORE 24 | 128355644 | Marietta |
ARDMORE 24 | 128566177 | Marietta |
ARDMORE 24 | 128842562 | Marietta |
ARDMORE 24 | 128958608 | Marietta |
ARDMORE 24 | 129765752 | Marietta |
ARDMORE 24 | 131003296 | Marietta |
ARDMORE 24 | 131113786 | Marietta |
ARDMORE 24 | 131136092 | Marietta |
ARDMORE 24 | 131145499 | Marietta |
ARDMORE 24 | 131491299 | Marietta |
ARDMORE 24 | 129488236 | Stratford |
ARDMORE 24 | 130928636 | Sulphur |
I tried using both the Table Visual and Matrix Visual
But unable to show everything in one column like below
now i need to show all the communities in a one column
Circuit_Name | CommunityName |
ARDMORE 24 | Ardmore,Marietta,Stratford,Sulphur |
Solved! Go to Solution.
Hi @Anonymous ,
Try this:
Column =
CONCATENATEX (
FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
'Table'[CommunityName],
","
)
If you only want to target Ardmore 24, please try add if statement.
Column =
IF('Table'[Circuit_Name] ="ARDMORE 24",
CONCATENATEX (
FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
'Table'[CommunityName],
","
),'Table'[CommunityName]
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this expression
List of Names = CONCATENATEX(DISTINCT(Table[Community Name]), Table[Community Name], ", ")
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
the only issue with the above query is
the list of communities is not the exactly same as ARDMORE
just for ARDMORE we have 4 different communities
but for other Circuit there are many
if i use the above query i am getting something like this
Hi @Anonymous ,
Try this:
Column =
CONCATENATEX (
FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
'Table'[CommunityName],
","
)
If you only want to target Ardmore 24, please try add if statement.
Column =
IF('Table'[Circuit_Name] ="ARDMORE 24",
CONCATENATEX (
FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
'Table'[CommunityName],
","
),'Table'[CommunityName]
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You could modify with this approach to show the names only when there is a reasonable number.
List of Names = var names = DISTINCT(Table[Community Name])
return IF(COUNTROWS(names)<=5, CONCATENATEX(names, Table[Community Name], ", "), "More than 5 names")
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |