Forum Discussion
Helper IIUsing 2 slicers on 1 report for multiple tables
Helper IIOk 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.
Super UserYes 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.
- PaulCo
I managed to get the desired slicers working by relating the Pipeline and the and Plan tables to the Link table and then linking the Dates table to the Plan table.
I now need to add a Bookings table from SFDC so I will see how that goes.
Thanks for your help.
- parry2k
Sounds good, let me know if need further help :)