Forum Discussion

marlonip's avatar
marlonip
Frequent Visitor
6 years ago
Solved

Filter all items based on a selection on a visual

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.

  • 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:

    RequestIDDestinationValue
    1Turkey1
    1Singapore2
    1Germany3
    2Belgium4
    2Turkey5
    2Singapore6

    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! 🙂

     

4 Replies

  • 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.

      • JarroVGIT's avatar
        JarroVGIT
        Resident Rockstar

        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:

        RequestIDDestinationValue
        1Turkey1
        1Singapore2
        1Germany3
        2Belgium4
        2Turkey5
        2Singapore6

        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! 🙂