Forum Discussion
Unique values dynamically linked to multiple value columns
Hi user180618
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
I've gone with extracting and concatenating the values, so now have:
| Name | Age | Location lived | Languages spoken |
| John | 30 | New York,Chicago | English,Italian |
| Ben | 35 | New York,Seattle,Portland | English,French |
| Jerry | 40 | San Francisco,Los Angeles | English,Spanish |
However, when I create a report and under Legend choose the column 'Location lived' and choose 'Count of Name' under Value, I cannot get it to show me the Location lived and Languages spoken values separately. So for example, I'd like a pie chart visualization for Ben to show me 1 count each for New York, Seattle and Portland, and 1 count each for English and French.
Is there a way to do this without running an R script in Query Editor?
Why don't you split the values by comma, so you can achieve it.
Did I answer your question? Mark my post as a solution!
Ricardo
I've already split them by commas in Query Editor (see previous post), but it comes out like this in the report:
I'd like it to show me the count value for those cities separately, but for the Name column to be the main aggregator.
Hi user180618
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
RicardoThanks camargos88. I've done as you suggested: Extracted and concatenated, and then Split Column by delimeter. But I now get several separate Locationslived.1 and Locationslived.2 columns.
My next question is: How should I group the columns so that the report can get all of those values together (instead of adding every split column to the Legend)? If in Query Editor I select Locationslived.1, Locationslived.2 and so on and group by Sum, I get an Error. If I group by All rows, I just get a [Table].
(Again, bearing in mind these Locations columns are multiple choice values relating to one Name column value)