Forum Discussion
Concatenated Lookup
Good morning,
I'm fairly new to Power BI and need some help, my apologies if this is a very newbie sort of question.
I have two tables with a relationship of one-to-many containing GHS codes called Hazard Codes (P301, P302 etc). The first table is the list of raw codes, descriptions and categories and the second table called GHST which contains a list of product codes with a concatenated field for GHS codes separated by a semi colon (P301; P302; H229; etc).
What I want to do is pull the description from the Hazard codes through to a visual containing the GHST fields in slicer form, so if a line is selected in GHST then a slicer visual displays all of the descriptions found for each of the codes.
Ideally each description would be on a separate line to make it look presentable but not a must.
Is this possible?
Example would be:
Product 1 > P301; H302; Description 1; Description 2
Thank you in advance
Hi PhillJK,
The main thing I would change here is the way the GHS codes are stored.
Because GHST contains something like "P301; P302; H229" in a single column, Power BI can't directly use the normal relationship to match each individual code to the Hazard Codes table.
I would create a separate bridge table by splitting that column using the semicolon delimiter. You would then have something like:
Product 1 | P301
Product 1 | H302
Product 1 | H229And your Hazard Codes table can stay as:
P301 | Description 1
H302 | Description 2
H229 | Description 3You can then relate the bridge table to the Hazard Codes table using the GHS Code.
Once that's done, selecting Product 1 will filter the bridge table to its three codes, which in turn filters the Hazard Codes table. You can simply put Hazard Codes[Description] into a table or slicer visual and you'll get the relevant descriptions.
If you want all the descriptions displayed together in one visual, you could also use a measure such as:
Hazard Descriptions =
CONCATENATEX(
VALUES('Hazard Codes'[Description]),
'Hazard Codes'[Description],
UNICHAR(10)
)This would display them like:
Description 1
Description 2
Description 3So yes, it's definitely possible. I would just avoid trying to do this with LOOKUPVALUE against the existing concatenated column. Splitting the codes into individual rows will make the model much easier to work with and will also give you the filtering behaviour you're looking for.
2 Replies
- ShahRukhSameer
Continued Contributor
Hi PhillJK,
The main thing I would change here is the way the GHS codes are stored.
Because GHST contains something like "P301; P302; H229" in a single column, Power BI can't directly use the normal relationship to match each individual code to the Hazard Codes table.
I would create a separate bridge table by splitting that column using the semicolon delimiter. You would then have something like:
Product 1 | P301
Product 1 | H302
Product 1 | H229And your Hazard Codes table can stay as:
P301 | Description 1
H302 | Description 2
H229 | Description 3You can then relate the bridge table to the Hazard Codes table using the GHS Code.
Once that's done, selecting Product 1 will filter the bridge table to its three codes, which in turn filters the Hazard Codes table. You can simply put Hazard Codes[Description] into a table or slicer visual and you'll get the relevant descriptions.
If you want all the descriptions displayed together in one visual, you could also use a measure such as:
Hazard Descriptions =
CONCATENATEX(
VALUES('Hazard Codes'[Description]),
'Hazard Codes'[Description],
UNICHAR(10)
)This would display them like:
Description 1
Description 2
Description 3So yes, it's definitely possible. I would just avoid trying to do this with LOOKUPVALUE against the existing concatenated column. Splitting the codes into individual rows will make the model much easier to work with and will also give you the filtering behaviour you're looking for.
- MFelix
Super User
Hi PhillJK ,
Not sure if I understand the way you data is setup, however what you are asking for a slicer to be filter by a value selected in a Matrix is not possible.
Altough you can have interactions between visuals, and you are able to slice and dice a visual by doing a selection in another visual, there is one exception that are the slicers.
Slicers due to it's nature of being used to filter you data can only be filtered by other slicers and not by any other visual, so in this case you cannot have one matrix that will filter out your values in the slicer.
Furthermore if the information in the Description is concatenated you cannot have the values on a slicer split by rows, for this you would need to redo the way the data is on your model.
Can you give some additional information about your model.
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!Proud to be a Super User!
Check out my blog: PBI Portugal
