Forum Discussion
Using 2 slicers on 1 report for multiple tables
I am trying to create a relationship between two data tables and a date table so that I can have slicers apply to each table.
I have one pipeline table from SFDC:
| Opportunity | Close Date | Region | Amount |
| 1 | 1/1/2017 | NA | 1000 |
| 2 | 12/1/2017 | EMEA | 100 |
| 3 | 1/15/2017 | EMEA | 2000 |
| 4 | 1/20/2017 | NA | 300 |
| 5 | 1/13/2017 | EMEA | 100 |
| 6 | 1/22/2017 | NA | 100 |
I have a Planning file in excel:
| Month | Region | Plan |
| 12/1/2017 | NA | 1500 |
| 12/1/2017 | EMEA | 500 |
| 1/1/2018 | NA | 2000 |
| 1/1/2018 | EMEA | 1000 |
I have a date file in excel:
| Date | Quarter | Week |
| 12/30/2017 | 4 | 52 |
| 12/31/2017 | 4 | 52 |
| 1/1/2018 | 1 | 1 |
| 1/2/2018 | 1 | 1 |
| etc |
Once linked I want to use slicers to filter the data tables. So if I clicked on Q1 and EMEA for instance I would see pipeline at 2100 and the plan at 1000.
I solved this problem previously by creating an intermediate table that linked the other tables and I wondered if this was still the best way to solve this issue as I did not use a date table previously:
| Month Region |
| 12/1/2017North America |
| 1/1/2018North America |
| 12/1/2017EMEA |
| 1/1/2018EMEA |
I then ensured this column was in each table.
The tables above have been simplified and contain a lot more columns that I will be needing to use and slice against and I may need to add more tables.
Thanks
5 Replies
You have some how need to link SFDC table with plannign table and to do so, you need field in both the table on which you can join and one of this must have unique value to set the relations.
So what you suggested is the way to go, concatenate field to set the relation.
I know SFDC will have opportunityID which will be unique but I think you doesn't have the same in planning sheet since it is maintain in excel otherwise that would have been the best way to do this.
Ok I've added the link table to link the Plan and Pipeline tables but how do incorporate the date table?
The date table can't be linked to both the Pipeline and Plan tables at the same time and if i break one relationship it affects how my slicers work.
Opportunity Close Month Region Amount Month Region 1 11/1/2017 NA 1000 11/1/2017NA 2 12/1/2017 EMEA 100 12/1/2017EMEA 3 11/1/2017 EMEA 2000 11/1/2017EMEA 4 11/1/2017 NA 300 11/1/2017NA 5 11/1/2017 EMEA 100 11/1/2017EMEA 6 11/1/2017 NA 100 11/1/2017NA Month Region Plan Month Region 11/1/2017 NA 1500 11/1/2017NA 11/1/2017 EMEA 500 11/1/2017EMEA 12/1/2017 NA 2000 12/1/2017NA 12/1/2017 EMEA 1000 12/1/2017EMEA I use the date table so that I can show things like period (FY18-Q1) in tables and the slicers. But currently it only slices the data where the relationship is present.
Yes you can link both "Close Month" from Opportunity and "Month" from plan
I assume one relationship will be inactive and you need to add measure in the table for which relationship in inactive and use userrelationship in your measure.
lets' assume you have inactive relationship with date on in plan table and your measure will look like this:
Total Plan = Calculate(Sum(Plan[Plan]), Userelationship(Plan[Month], Date[Date]))
Add table visual, drop date from date table, amount from opp table and aggregate it to sum and "Total Plan" (new measure) and it should work.
