Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ValentinBIA
Resolver I
Resolver I

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
Anonymous
Not applicable

 

 

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

Hi @Anonymous,

 

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

Anonymous
Not applicable

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.

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

Anonymous
Not applicable

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.