Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Have column with multiple values in PowerBI

I have a column with the following data

IDCustIDVisitedCountries
11Holland
21Belgium
31Germany
42Holland
52Belgium

 

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:

VisitedCountryCount of CustID
Holland2

 

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
Holland2
Belgium2
Germany1

So Customers that have visited Holland have also visited Belgium and Germany.

 

Can this be achieved in PowerBI?

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

I thought of a way:

First, copy the original data table in Edit Queries:

hh6.PNG

Second, create relationship with [CustID] column:

hh7.PNG

Third, create a measure:

 

Count of CustID = 
CALCULATE(
    COUNT('Sheet1 (2)'[CustID]),
    ALLSELECTED('Sheet1 (2)'[ID])
)

 

Fourth, create visualization:

hh8.PNG

Note: the two [VisitedCountries] columns are from different tables.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

I thought of a way:

First, copy the original data table in Edit Queries:

hh6.PNG

Second, create relationship with [CustID] column:

hh7.PNG

Third, create a measure:

 

Count of CustID = 
CALCULATE(
    COUNT('Sheet1 (2)'[CustID]),
    ALLSELECTED('Sheet1 (2)'[ID])
)

 

Fourth, create visualization:

hh8.PNG

Note: the two [VisitedCountries] columns are from different tables.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Thank you! This works fine for me.

amitchandak
Super User
Super User

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 Linkedin

Anonymous
Not applicable

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

 

Anonymous
Not applicable

Hey,

 

thanks for your answer. But this isn't what I meant...

Anonymous
Not applicable

Haha alright, I didn't get the question earlier

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.