Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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 Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This works fine for me.
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
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
Hey,
thanks for your answer. But this isn't what I meant...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |