Select a group of users among a bigger data

[patricia_csf]

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.

[v-jingzhang · Accepted solution]

Hi @patricia_csf

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.

[amitchandak]

@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 ))