Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I've included a DAX function in data model, which labels Company Name based on Company ID found in another column (namely Relationship a referenced column). Within this Relationship column, there could be 1, 2 ,3 or up to 6 different company ids in one cell. Which formula or DAX function I can use to have this company name column to include multiple company names.
For example, Etsy is assigned 1, apple is assgined 2, amazon is assigned 3, oatly is assigned 4, ford is assigned 5 and toyota is assigned 6. However, only one value shows for each cell. There should be multiple values showing for some cells. As it applies to this example, 1 2 3 is found in the referenced column, but only assigning the value of Etsy instead of Etsy Apple AmazonHow do I show multiple values in one cell, based on the multiple conditions? Thanks.
You can reter to the following example
You can create two blank query and put the following code to advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXItKa5UUIrViVYyAvISCwpyUsE8YxAvN7EqPw/MNQFy8xNLcqBqTYHctPyiFKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Companyid = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Companyid", Int64.Type}, {"Name", type text}})
in
#"Changed Type"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUMFaK1YlWSgKzjcDsZCDbRMFUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customerid = _t, Companyid = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Companyid", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Companyid.1", "Companyid.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Companyid.2", Int64.Type}, {"Companyid.1", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customerid"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Value"}, Query1, {"Companyid"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Name"}, {"Name"}),
#"Grouped Rows" = Table.Group(#"Expanded Query1", {"Customerid"}, {{"Count", each Text.Combine([Name],"," ),type text}})
in
#"Grouped Rows"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous thanks for providing solution.
I am still trying to understand the query you have, looks like you have to create a lookup table which is customer id and then use that as a lookup value for another query? I still don't know how it will apply to my case. For example, for the below table, what I want to achieve is for the Company Name column, I want to be able to have the company name assigned based on the company ID in the Relationship column. Currently I use DAX containsstring which only assign one company name for each cell although there could be multiple company ID in the relationship company column. Any ideas how to go about this?
Relationship Company | Company Name |
1 | Etsy |
1,2 ,3 | Etsy, Amazon, Apple |
3,4,5 | Apple, Oatly, Ford |
2,3,6 | Amazon, Apple, Toyota |
1,2,3,4,5,6 | Etsy, Amazon, Apple, Oatly, Ford, Toyota |
How would you like to find the company name? Is your company ID column and company name column in the same table, the data you provided may seem incomplete, can you provide some more detailed example data?
Best Regards!
Yolo Zhu
hi @Anonymous yes. the company ID column and company name column should be the same table. Basically, I want to create a calculated "Company Name" column which multiple conditions based on the company ID column, however, if the containsstring I currently have, it only give one value (company name) in each cell. Meanwhile, in the company ID column, there could be multiple IDs. For example, 1,2,3 or 3,5,6, I want the campany name column to have Etsy,Amazon,Apple or Apple, Ford, Toyota instead of just one company name.
Thanks so much for your help!
Can you provide some sample data so that can provide more suggestion for you
Best Regards!
Yolo Zhu
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |