Forum Discussion
Concatenate values despite filter
Hi there,
I have two measures (A, B). They both have tags (positive, negative, neutral):
Now, I want to be able to filter for a specific tag and show next to the measure a concatenation of ALL tags of that measure.
When I filter on the tag "neutral" currently I'm only getting the tag neutral next to the measure.
The final "Tag Name" I want to obtain should "positive, negative, neutral" since in the data set the measure A has all tags.
Any idea how to approach this?
My Power BI Example file is uploaded here: https://easyupload.io/2h5y0d
Thanks!
Hi HR3038511,
You can approach this problem by creating another table containing the measure and the concatenated tags and using relationships to display correct values on filtering.
The steps are explained below. To implement the below steps, please refer to the pbix file attached.
- Create a concatenated tags table - Using the original Measure Table (I added the Tag Name field to it), create another duplicate table and group by the table on Measure such that you get concatenated tags along with it.
Measure Table
Concatenated Tags Table
- Create a relationship between the concatenated tags table and the measure table on the measure field. Set the direction of filtering to both sides.
- In the table visual, use the fields Measure and TagList from the Concatenated Tags table.
- For filtering, use the Tag Name from the Measure table
Due to the relationships, the Tag Name from the Measure table should filter the corresponding Measure and subsequent Tag List in the Concatenated Tags table.
Examples:
Please refer to the pbix file attached - https://easyupload.io/xnlt7w
Let me know if you have any questions!
Hi HR3038511
Thanks for the reply from Bipin-Lala , please allow me to provide another insight:
Add a measure:MEASURE 2 = VAR _currentMeasure = SELECTEDVALUE ( 'Measure Table'[Measure] ) RETURN CONCATENATEX ( FILTER ( ALL ( 'Measure Table' ), 'Measure Table'[Measure] = _currentMeasure ), [Tag Name], "," )Then add the measure to the table visual, the result is as follow:
PBI file attached, hope this helps.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi HR3038511,
You can approach this problem by creating another table containing the measure and the concatenated tags and using relationships to display correct values on filtering.
The steps are explained below. To implement the below steps, please refer to the pbix file attached.
- Create a concatenated tags table - Using the original Measure Table (I added the Tag Name field to it), create another duplicate table and group by the table on Measure such that you get concatenated tags along with it.
Measure Table
Concatenated Tags Table
- Create a relationship between the concatenated tags table and the measure table on the measure field. Set the direction of filtering to both sides.
- In the table visual, use the fields Measure and TagList from the Concatenated Tags table.
- For filtering, use the Tag Name from the Measure table
Due to the relationships, the Tag Name from the Measure table should filter the corresponding Measure and subsequent Tag List in the Concatenated Tags table.
Examples:
Please refer to the pbix file attached - https://easyupload.io/xnlt7w
Let me know if you have any questions!
Hi HR3038511
Thanks for the reply from Bipin-Lala , please allow me to provide another insight:
Add a measure:MEASURE 2 = VAR _currentMeasure = SELECTEDVALUE ( 'Measure Table'[Measure] ) RETURN CONCATENATEX ( FILTER ( ALL ( 'Measure Table' ), 'Measure Table'[Measure] = _currentMeasure ), [Tag Name], "," )Then add the measure to the table visual, the result is as follow:
PBI file attached, hope this helps.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.