Forum Discussion
Have column with multiple values in PowerBI
I have a column with the following data
| ID | CustID | VisitedCountries |
| 1 | 1 | Holland |
| 2 | 1 | Belgium |
| 3 | 1 | Germany |
| 4 | 2 | Holland |
| 5 | 2 | Belgium |
My goals is to use a slicer for countries. Let say, the slicer is filtered on Holland normally it will filter the visual with data from above on Holland and it would look like this:
| VisitedCountry | Count of CustID |
| Holland | 2 |
But I would like to see, in the same visual, the other countries that customers visited. (with the slicer selected on Holland)
VisitedCountry | Count of CustID |
| Holland | 2 |
| Belgium | 2 |
| Germany | 1 |
So Customers that have visited Holland have also visited Belgium and Germany.
Can this be achieved in PowerBI?
Hi Anonymous ,
I thought of a way:
First, copy the original data table in Edit Queries:
Second, create relationship with [CustID] column:
Third, create a measure:
Count of CustID = CALCULATE( COUNT('Sheet1 (2)'[CustID]), ALLSELECTED('Sheet1 (2)'[ID]) )Fourth, create visualization:
Note: the two [VisitedCountries] columns are from different tables.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Absolutely!!
Step 1: Drag a TABLE visual into the canvas.
Step 2: Add CustID and vistingcountries in the values section of the visual.
Your expected result should already be visible.
Though It is not clear what you meant by this
"when I have a countries slicer when Holland is selected, I would like to show the amount of customers visited Holland and amount of customers who visited Holland and other countries"
Let me know if this helps.
Regards,
Rakesh
I did not try. But it should work like this. The first summarize in a variable, I have doubt. Will that work in "In clause " or not
New Table = var _selectCust = summarize(filter(ALLSELECTED(TableID[CustID]),'Table'[VisitedCountry] in ALLSELECTED('Table'[VisitedCountry])),TableID[CustID]) return union( summarize(table, VisitedCountry, "Visit Count"count(table[TableID])), summarize(filter(all(table),TableID[CustID] in _selectCust), VisitedCountry, "Visit Count"count(table[TableID])) )Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinHi Anonymous ,
I thought of a way:
First, copy the original data table in Edit Queries:
Second, create relationship with [CustID] column:
Third, create a measure:
Count of CustID = CALCULATE( COUNT('Sheet1 (2)'[CustID]), ALLSELECTED('Sheet1 (2)'[ID]) )Fourth, create visualization:
Note: the two [VisitedCountries] columns are from different tables.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
