Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PaulCo
Helper II
Helper II

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:

 

OpportunityClose DateRegionAmount
11/1/2017NA1000
212/1/2017EMEA100
31/15/2017EMEA2000
41/20/2017NA300
51/13/2017EMEA100
61/22/2017NA100

 

I have a Planning file in excel:

 

MonthRegionPlan
12/1/2017NA1500
12/1/2017EMEA500
1/1/2018NA2000
1/1/2018EMEA1000

 

I have a date file in excel:

 

DateQuarterWeek
12/30/2017452
12/31/2017452
1/1/201811
1/2/201811
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 5
parry2k
Super User
Super User

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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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. 

 

OpportunityClose MonthRegionAmountMonth Region
111/1/2017NA100011/1/2017NA
212/1/2017EMEA10012/1/2017EMEA
311/1/2017EMEA200011/1/2017EMEA
411/1/2017NA30011/1/2017NA
511/1/2017EMEA10011/1/2017EMEA
611/1/2017NA10011/1/2017NA

 

MonthRegionPlanMonth Region
11/1/2017NA150011/1/2017NA
11/1/2017EMEA50011/1/2017EMEA
12/1/2017NA200012/1/2017NA
12/1/2017EMEA100012/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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

Sounds good, let me know if need further help 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors