cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ValentinBIA Regular Visitor
Regular Visitor

Slicers and Lookup Tables

Hi Power BI communitity !

 

I'm currently working on building a report for car rental stats.

 

Following is an example of my dataset:

Journeys  
Station FromStation ToNb of Rentals
AlphaAlpha6
AlphaBeta13
AlphaGamma36
AlphaDelta10
BetaAlpha23
BetaBeta11
BetaGamma20
BetaDelta30
GammaAlpha7
GammaBeta18
GammaGamma30
GammaDelta32
DeltaAlpha31
DeltaBeta12
DeltaGamma34
DeltaDelta35

 

And here is my lookup table:

Locations   
StationLongLatCity
Alpha-627-213Windhelm
Beta-1156575Morthal
Gamma548-1327Markarth
Delta-2181490Riften

 

On my report, I have a slicer that lets me select one station.

And I need to build 2 pie charts according to this slicer:

- The first one needs to show where the cars go when they leave the selected station (% repartition of the destinations).

- The second one needs to show where the cars come from when they arrive to the selected station.

 

The issue that I am currently experiencing is that whenever I select one station in the slicer, both pie charts only show the selected station.

I have tried:

- Using measures, with ALL and ALLEXCEPT, but nothing conclusive there (at least with my knowledge of DAX)

- Creating a second lookup table, and having a StationTo and StationFrom lookup tables, that gets one of the pie charts to work, but not the other one. And I could use two different slicers, but I would like to avoid that.

 

I am not sure there is a solution for my problem, but before giving up, I wanted to ask the community.

 

I hope my explanation is clear, don't hesitate to ask me precisions if you are not sure.

 

Thank you very much !

 

Valentin

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Slicers and Lookup Tables

 

 

Ok yes you are right, i have told you to remove the "TO" and "FROM" columns from the duplicated tables.  That is certainly incorrect for what you are trying to achieve.

 

Remove that step from your data import.  The same table relationship design is important but choosing the correct column to link to the Locations table will be important.  So on the "TO" table, link the TO column.  On the TO chart, you'll use the FROM column for the locations.  The same (in reverse) is true for the FROM table.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

5 REPLIES 5
Ross73312 Super Contributor
Super Contributor

Re: Slicers and Lookup Tables

The issue is that you are mixing two different datasets within the same table.  Whenever you filter for "From" you are also filtering your data for "To".

 

 

I would suggest going into Edit Queries and create 2 new tables, one for "From" and one for "To" by right clicking on your existing table in the query list and selecting "Reference".  Next disable your existing table from loading.  In your "From" and "To" tables. simply remove the unnecessary column.

Now you just need to link both of these tables to your Locations table in the relationship editor and make sure that the location table is the one in your slicer.

 

Now just update your charts to be pointing to the correct tables.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ValentinBIA Regular Visitor
Regular Visitor

Re: Slicers and Lookup Tables

Hi @Ross73312,

 

Thank you for your answer.

 

But I tried your solution and it is still not working.

See the issue here is that I need to keep the link between stations to and from.

What I am interested in is knowing where the cars come from when they arrive to a specific station and where they will go next when they leave this station. And all of it by selecting the station through a slicer if possible.

 

Do you want me to try to reproduce a visual of what I expect if this is not clear ?

 

Thank you very much,

 

Best,

 

Valentin

Ross73312 Super Contributor
Super Contributor

Re: Slicers and Lookup Tables

That is where the Table Relationships come in.  This is why you need to link both the To and From tables to the location table.  Then if all of your Location related slicers must come from the location table.  This will allow you to choose correctly.

 

Also when you set up your charts, the location in all of your charts must be from the location table.  This will let it calculate your TO and FROM correctly and let your data match up.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ValentinBIA Regular Visitor
Regular Visitor

Re: Slicers and Lookup Tables

I did exactly everything you wrote, and it still does not work.

 

Whevener I select a city, both charts give me 100% for this city

Ross73312 Super Contributor
Super Contributor

Re: Slicers and Lookup Tables

 

 

Ok yes you are right, i have told you to remove the "TO" and "FROM" columns from the duplicated tables.  That is certainly incorrect for what you are trying to achieve.

 

Remove that step from your data import.  The same table relationship design is important but choosing the correct column to link to the Locations table will be important.  So on the "TO" table, link the TO column.  On the TO chart, you'll use the FROM column for the locations.  The same (in reverse) is true for the FROM table.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 141 members 1,638 guests
Please welcome our newest community members: