Forum Discussion
Lookup from multiple values in same column
Hi all,
Stumped on this one and need some help.
I'm trying to concatenate ID numbers based on combinations of units they can take. This is all currently in one table.
So, we have this kind of data:
ID | Unit |
| 1 | x |
| 2 | x |
| 3 | y |
| 3 | x |
| 2 | z |
| 1 | y |
| 2 | y |
| 3 | z |
These units can be arranged into certain combinations. For example, XY, YZ, XZ etc. These are in their own table like this:
| Comb 1 | Comb 2 |
| X | Y |
| Y | Z |
| X | Z |
What I need is this:
| Comb 1 | Comb 2 | IDs |
| X | Y | 1, 3, 2 |
| Y | Z | 3, 2 |
| X | Z | 2, 3 |
So that the combinations concatnate all IDs that have those combinations of units specified on that row. I need to be able to iterate over my initial data table, in the format of the first table above, and then say in this new table: "Where a student has both X and Y units, concatenate their ID into this column."
Lord, i hope that makes sense. My mind is boggled already, and any help would be so greatly appreciated.
5 Replies
Perhaps:
IDs = IDs1 = CONCATENATEX(FILTER('Units','Units'[Unit] = [Comb1]),[ID],", ") IDs2 = CONCATENATEX(FILTER('Units','Units'[Unit] = [Comb2]),[ID],", ") RETURN IDs1 & ", " IDs2To build on Greg's post, if you first need to generate the table with the combinations of Units, you can do that in the query editor. Here is one way to do it, using your sample data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUapQitWJVjKCs4yBrEo4CyFbBWYZwmWNUNQBZWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Unit = _t]),
basetable = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Unit", type text}}),
Units = List.Distinct(basetable[Unit]),
#"Converted to Table" = Table.FromList(Units, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "AddUnitsList", each Units),
#"Expanded AddUnitsList" = Table.ExpandListColumn(#"Added Custom", "AddUnitsList"),
#"Filtered Rows" = Table.SelectRows(#"Expanded AddUnitsList", each ([Column1] <> [AddUnitsList])),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "SortedList", each List.Sort({[Column1], [AddUnitsList]})),
#"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"SortedList"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"SortedList"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Unit1"}, {"AddUnitsList", "Unit2"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Unit1", type text}, {"Unit2", type text}})
in
#"Changed Type1"It results in this table, with which you can then do a concatenatex expression.
Let me know if you're interested to take this query further to also generate the final table (an M approach vs. DAX). I started doing a CROSSJOIN() in DAX to make the table above. It is doable, but doing it in the query was easier (at least for me).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Interesting request, I'll have a think. Can I ask why you want it displayed in this way? It may help the community figure out how to solve it if we understand the business requirement or reasoning, and what the restrictions are.
If I am understanding the requirement correctly, this will work, I'm just not sure if there's a more efficient way to do it:
IDs =CONCATENATEX (NATURALINNERJOIN(SUMMARIZE (FILTER ( 'Fact', 'Fact'[Unit] = Comb[Comb 1] ),'Fact'[ID]),SUMMARIZE (FILTER ( 'Fact', 'Fact'[Unit] = Comb[Comb 2] ),'Fact'[ID])),'Fact'[ID],", ")Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Thats it AllisonKennedy !
The purpose was for me to be able to see how many students were enrolled on this particular combination of units, as they are options, and we use a clustered approach to teaching. Using this report we will be able to see which combination is more popular than others and diversify.
A slightly more esoteric question, but could you please explain how you understood what the steps to solve this were? I'm trying to work on building my own problem solving in PBI.
EpicTriffid Glad it worked!
To answer your question of how I got there, I just finished teaching day 1 of 'Mastering DAX' course today, so I'd say my steps to the problem solving are that I know a lot about DAX and Power BI. It's a bit tricky to explain in one simple post, but the key is to have a solid understanding of the Row and Filter context within DAX, how that fits in with the relationships in your data model, and what you are trying to achieve. Keep practicing and reading blogs and posts on this forum and you'll slowly get there, or find a course that meets your skill level.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
