Forum Discussion
How to apply a slicer from one table to another table
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.
Community ChampionOk, perhaps I am getting closer to your goal. Download the PBIX again from the link above.
So...
- I added a new measure called Obligations by Year.
- Selected a year in the slicer
- 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.
- edhans
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