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.
Hi,
I have a table, with 100 users that travelled 1,000 trips (10 trips on average per person).
Among them, 10 went to New York, e.g. 30 trips (3 on average per person).
I can select both data in the table. So far, so good.
What I need help is to select, among ONLY these 10 users that travelled to New York (30 trips), how many trips they also had to other places and which places were that.
E.g.
User 1 went 10 times to NY, and also 3 times to California
User 2 went 1 time to NY, and also 30 times to China
User 3 went 5 times to NY, and also 1x to California and 1x to China
User 4 went 2 times to NY, and 20 times to Brazil...
Thx in advance.
Solved! Go to Solution.
I used the following sample data to realize it, please refer to the steps below and let me know if it helps.
First create a new table with the following DAX codes to filter the trips of those users who travelled to NY.
NY_User_Trips =
FILTER (
Trips,
Trips[User]
IN (
SUMMARIZE ( FILTER ( Trips, Trips[Destination] = "New York" ), Trips[User] )
)
)
Then use a Matrix visual, put User as Rows, Destination as Columns, Count of Destination as Values (click the drop-down arrow to change Destination's aggregate type into Count) from the new table.
If you don't want to show NY trips, you can apply a Destination filter on the visual with NY unselected. Otherwise, you can also modify the new table DAX codes into:
NY_User_Trips =
FILTER (
Trips,
Trips[User]
IN (
SUMMARIZE ( FILTER ( Trips, Trips[Destination] = "New York" ), Trips[User] )
)
&& Trips[Destination] <> "New York"
)
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
I used the following sample data to realize it, please refer to the steps below and let me know if it helps.
First create a new table with the following DAX codes to filter the trips of those users who travelled to NY.
NY_User_Trips =
FILTER (
Trips,
Trips[User]
IN (
SUMMARIZE ( FILTER ( Trips, Trips[Destination] = "New York" ), Trips[User] )
)
)
Then use a Matrix visual, put User as Rows, Destination as Columns, Count of Destination as Values (click the drop-down arrow to change Destination's aggregate type into Count) from the new table.
If you don't want to show NY trips, you can apply a Destination filter on the visual with NY unselected. Otherwise, you can also modify the new table DAX codes into:
NY_User_Trips =
FILTER (
Trips,
Trips[User]
IN (
SUMMARIZE ( FILTER ( Trips, Trips[Destination] = "New York" ), Trips[User] )
)
&& Trips[Destination] <> "New York"
)
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@patricia_csf , Try a measure like
new measure =
var _usr = summarize(filter(Table, Table[City] = "New York" ), Table[User])
return
calculate(count(*) , filter(Table, Table[City] <> "New York" && Table[User] in _usr ))
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |