Filter all items based on a selection on a visual

[marlonip]

Hey guys!

I know the subject may seem as somenthing obvious or irrelevant, but I'll try to clarify on the thread.

Here's the cenario:

I'm working with a dataset containing all the approved travel requests from a certain period of time. Each request can have a single or several destinations for a single or several passengers. Based on this dataset, I created a simple visual containing a map so the manager can see what countries represents the most significant expense value and a table containing the detailed information for each of the requests so he can see what are the requests and who is traveling for each country. 

As I explained, each request can have multiple destinations, so when we interact with the map, only the line that have that specific destination appears on the table. What my manager requested is to alter the table, that have the detailed travel information, in a way that, when we click on a country on the map, not only the single line of the travel request appear, but all the lines that are related to the request that have the selected destination.

To help you guys understand what I'm trying to accomplish, here's an example from my dash:

This is the map and the table as is.

Suppose I want to see who travelled to Turkey, so I click on the point on the map 

Basically we have 3 different requests that went to Turkey. Now, what I need (using request 20030 as an example) is to bring, when I click on Turkey, all the legs for each request, that would look something like below (remembering that I manually filterd only the request 20030).

As we can see, the request 20030 have 3 different legs, having one of them going to Turkey. The same happens for the other 2 request as shown in the second picture, so the result I need is the table from the second picture showing not 3, but 9 lines (3 lines for each request id).

At this point, I'm considering reporting this to my manager as something impossible to achieve on PBI, so this community is my last hope. If someone have any clues to how I could make this work, I'd really apreciate the input.

I wish to all a merry Christmas (a little late) and a happy and prosper new year.

[v-joesh-msft]

Hi @marlonip ,

Could you please share your sample pbix file if you don't have any Confidential Information.?

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marlonip]

Hi @v-joesh-msft.

Here's my pbix file: https://drive.google.com/open?id=1f-nUq5Sj-Xh9l32TThmL0Pf4cVEehmMG 

If you have any doubts (my model is in portuguese), please, feel free to reach out to me.

Thank you very much!

[JarroVGIT · Accepted solution]

@marlonip, the funniest thing happened. I came across your question last week and thought about it a bit back then but couldn't think of an approriate answer for you. But just now, it came to mind and I had to test it out and it works!

I used the following test dataset:

RequestID	Destination	Value
1	Turkey	1
1	Singapore	2
1	Germany	3
2	Belgium	4
2	Turkey	5
2	Singapore	6

Now, don't mind the Value column, we are interested in the RequestID and Destination. I loaded the above in PBI and changed the data category of the Destination column to 'Country'. Then I duplicated the table by creating a calculated table like this:

Table_Copy = Table_Orig

It is a one on one copy of the original table. Then, I created a many-to-many relationship like this:

Now I created a MAP visual based on Table_Orig and a TABLE visual based on Table_Copy. When I select Belgium in the MAP, I want to see everything of RequestID=2 in the TABLE. If I select Germany, I want to see RequestID=1 and if I select Turkey or Singapore I want to see everything, and that works! 😄 (I am happy because I was bumped out I wasn't able to figure it out in the first place while the answer is not as complicated as I initially thought it would be).

Nothing selected, the Table shows everythingGermany selected, table only shows RequestID=1

See my PBIX here: https://1drv.ms/u/s!Ancq8HFZYL_aiIgd8_NWbpeOz2QvWw?e=SdfUgB

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[marlonip]

@JarroVGIT, you're a life saver! Thank you very much! I had to make a few adjustments on my model, but your suggestion was my point of start for the solution based on my data.

The only problem, though, is related to our local report server, which is compatible only with updates until September 19 and doesn't have many-to-many relationships available, so now I'm trying to adapt the fully working PBI to the earlier version to be able to publish for others to see the dashboard, but this is an issue for another thread.

Once again, thank you very much!