How to apply a slicer from one table to another table

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. 

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

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!

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. 

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.

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.

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.

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.

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.

Thanks a lot edhans