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

How to apply a slicer from one table to another table

Hi there, I am new to Power BI and do need some help on how to link a slicer from TableA to TableB. Basically, the slicer is coming from Type column from TableA.  What I need is when the user select an option as FY1 or FY2, TableB will be filtered by matching their ID.  For example, when FY1 is selected, the TableB will be filtered as

1 10

1 20

2 30

2 10

Join TableA and TableB does not work, as ID are not unique is either A or B.

The goal to get the sum of 10,20,30,10 when FY1 is selected, or 5,5,20,10 when FY2 is selected.

 

Any help is greatly appreciated.

 

Capture.PNG

19 REPLIES 19
Frederick_Syd
New Member

Building a Bi-directional relationship can fix it easily.....

Greg_Deckler
Super User
Super User

@Anonymous Are the two tables related to one another on ID? If so that should happen automatically.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks for all reply.  They are related by ID, but we cannot join them together, as ID from either table are unique.  The sum of amount on joined table will not be corrected due to the duplication records created by join.

jthomson
Solution Sage
Solution Sage

Is there any reason you can't de-dupe table A so that the ID value is unique?

Anonymous
Not applicable

we cannot de-duplication.  The tables only illustrated the concept for this, the actual ones have many other columns.

@Anonymous you should create a new ID table that would be a bridge table that would contain the unique ID numbers. That becomes your DIM table. You then relate that (it isn't a join, it is a filter relationship) to each of the ID columns in your two other tables. Then, use the ID from the bridge table in your slicer and in all visuals where the ID value would be used. You should, in fact, hide the ID field in the two other tables. 

 

All of your visuals would just work at that point, and any related DAX becomes much easier to deal with. See the link below for more detailed guidance on avoiding Many to Many relationship, which is what would have to happen if you don't create the bridge table. To create the bridge table, you can do this easliy in Power Query by creating a reference from your FACT table, remove all columns but the ID column, then right-click on ID and "remove duplicates".

 

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks for your great help.  Based on what I provided these two simple tables, would you please provide some step by step procedures to achive this?  Thank you very much.

Hi @Anonymous - see my attached PBIX file. You will wind up with a model that looks like this.

edhans_0-1600116911736.png

You can see the code in the UniqueTable query that transforms the ID columns from both tables into a single unique column for your slicer.

 

If you need further help, please provide sample data using the directions in the links below. I cannot work with images of data. Images are great for expected results, but do not help with providing data to work with.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans Hi there - first I really appreciate your great effort to help me out on this issue.  I review your reports.  The methods these tables created are really helpful.  However, it does not resolve my issue.  Our situation is to use Stuff of Table1 as the slicer, rather than the ID of the UniqueTable, so that when a Stuff is choosen both tables 1 and 2 will be filtered.  

From the screen shot you can see that when the slicer on the most right is choosen as 'a', only table 1 is filtered but not table 2.

 

Is this kind of application approachable, i.e. one slicer from one of two unrelated tables can filter the other table?

Again, thank you very much!

 

Capture.PNG

Why are you using the value from Stuff as the slicer? Why not use the ID from the bridge table? 

Look at the filter relationships in my image above. The bridge table filters both of the other tables, so it should be used as the slicer. I guess I am not understanding what you are trying to accomplish. I am trying to use best practices, and that generally means you use fields from your DIM tables, not FACT tables, as slicers, filters, and fields in visuals. FACT tables do not filter anything (or shouldn't) so those fields are of limited use, other than generic reporting as another column in a table, or providing the "many" side of a 1 to many filter relationship.

 

Maybe share your PBIX with data that is internal to the file (paste tables in the "Enter Data" function vs linking to files/source in Power Query) as I have done so it is all contained and I can take a look at it, and manipulate the M code if necessary.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thank you so much.  Yes, we do need to filter by Staff from table 1.  In my situation, we cannot use the unique ID to filter both.  The reason behind this is that the user does not care of ID, but only the Staff, plus there are many IDs under each Staff.  The situation is simple and straight forward, but I stuck here for many days.   Do you think this is approachable?

 

If not, let's go back to my Table A and B.  My target is to show the Sum of Amount in Table B under each Type in Table A.  We can simply join Table A and Table B as TableAB.  But TableAB will have duplicatate records.  Taking ID = 2 as an example, it will have 2-30, 2-10, 2-30 and 2-10 in TableAB.  When sum, the amount becomes 80, rather than 40.  If there are way to get 40 from TableAB?

 

Again thank you very much. 

 

 

 

 

Anonymous
Not applicable

Thank you @edhans very much for your diligent on following up this issue.  I put one report and one Excel file in One Drive.  Hopefully you can access it. 

The report - https://1drv.ms/u/s!AlntzgQcvbvR2D4dmQDGa_jCakw7?e=thmaCs

The excel tables - https://1drv.ms/x/s!AlntzgQcvbvR2D0cnh_VTTAaN2Qd?e=G2LT3i

From below, you can see Table1 has ERCode, AllocationAmount adn EventFY, Table2 has ERCode and ObligationAmount.  The purpose of this report is to show The sum of Obligation Amount in Table2 by a slicer of EventFY in Table1. Note we cannot join Table1 and Table2 by ERCode as it is not Unique, and join will duplicate records, thus make the sum not correct.  As the reports shows, if you click the Slicer, only the Visual of Table 1 changes, Table2 does not, as EventFY is coming from Table1.

 

If we join Table1 and Table2 by ERCode, and have duplicate records created, do we still can get the Sum(ObligationAmount) correct by certain kind of measure or transformation?  I tried to create another table based on the joined one, in which Distinct ERCode and ObligationAmount is selelcted.  But the visual of this table does not change after the slicer of EventFY is changed.  Basically, I am asking you that if 1 slicer for 2 tables will not work anyway, is there any other way to get the sum right?

 

As I said earlier that the application situation is simple and straight forward, but it is really hard to get it done.

 

Thanks!

 

Capture.PNG

 

 

 

Try this @Anonymous 

I still am not 100% sure on your data. There are a number of ER Codes in Table2 not in Table1, so selecting some codes returns a blank in the table 1 visual. We can work through that. 

edhans_0-1600212053744.png

Above is an image of the model.

  1. I added a date table. That is now the "year" you should be slicing on. Always try to use FACT tables to manage your slicers, and just about any model with a date can benefit from a date table.
  2. I added the ER Codes table which is a distint list of all ER codes from tables 1 and 2. It can be just table 2 if you want. It could be table 2 as long as it shows up in table 1 as well. This is what I don't know enough about your business and your use case.

Here is my PBIX file.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thank you very much!  I do not know how this simple situation is hard to understand.  Table1 has EventFY and ErCode, table2 has ERCode and ObligationAmount.  I want to sum Obligation Amount based on a selection of EventFY. The logic is that a set of ERCode will be defined by a selection of EventFY, then we need to selelct all records in Table2 with ERCode falls in the selection.  It is just like a two step slicers integrated into one.  

 

The table schemes I provided are already very simple.  It should not be hard to understand what I am trying to reach.

Ok, perhaps I am getting closer to your goal. Download the PBIX again from the link above.

edhans_0-1600223422312.png

So...

  1. I added a new measure called Obligations by Year.
  2. Selected a year in the slicer
  3. Totaled the obligations by year based on the ER codes in table 1 using them as a filter for table 2 er codes. 

The measure is:

 

Obligations by Year = 
CALCULATE(
    SUM(Table2[ObligationAmount]),
    TREATAS(
        VALUES(Table1[ERCode]),
        Table2[ERCode]
    )
)

 

Is that better or closer? 
If not, can you provide a finished result mockup from Excel. SHow me what the answer should be if I select 2020 as the year for the total obligations if what I have provided isn't right, and how you got there.

I apologize I'm not getting your meaning. I think there is a bit of a language issue here, and sometimes when Person A describes a calculation to Person B, Person A thinks it is the clearest explanation in the world, but Person B has no context of the question and is unable to completely piece it all together.

 

So in this case, Person B, or me, is having to take multiple runs at it to narrow down the exact request. 😁 So be patient with me please.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans Thank you very much for your time on this.  Unfortunately, sum over ERCODE is not our situation.   I updated the report and excel file.  You can get them from (excel) https://1drv.ms/x/s!AlntzgQcvbvR2D0cnh_VTTAaN2Qd?e=Tsa89V and (report) https://1drv.ms/u/s!AlntzgQcvbvR2D4dmQDGa_jCakw7?e=Ly9r4s

 

The following shows what we want to reach.  A slicer (Event FY) is from Allocation table. When 1990 is selected, the Obligations should show 600 and 1300 for A and B, respectively, and  900, 800 when 1991 is selected.  The link between these two tables are ERCODE.  But we cannot join them together.  If join, the sum of Obligation from the join table will not be right.

Hopefully, this description is clear enough to present our situation.  Agai, we appreciate your great help on this.

 

Capture.PNG

I apologize @Anonymous . You may want to ask someone else. I just spent 15 min trying to rename the tables and fields in my exisiting model to the new worksheet names and field names in your latest data and my model has errors and my measures all blew up. I'd have to start from scratch.

 

It is really important to be as clear as possible on the initial request, and your last post probably has everything that is necessary for someone to finish it, but I'm kind of burnt out on it with the source data changing as it did, and new fields (where did State come from?) being added.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks a lot @edhans 

Can you provide some good data? This is a modeling issue. The ID in the DIM table is waht is needed, but we can include other columns in that table. As you say, the user doesn't care about the ID. We can use data from your two tables to create a more comprehensive bridge table with more than just one column. Please use the links below to share data, or share an Excel file (or text or whatever) with some good sample data.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

Top Solution Authors
Top Kudoed Authors