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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cross filtering with one fact and multiple dimension tables

Hello,

 

I have the following situation:

 

- one fact table with one measure (price)

- one dimension table with agents

- one dimension table with teams

- one dimension table with workgroups

 

All three dimension tables are joined on the fact table (star schema). There is a hierarchy between agent and team and between agent and workgroup but this hierarchy is through the fact table. The hierarchy is not in one table.

 

Now I want to filter on one specific agent and see the average price of that agent but also the average price of his team(s) and his workgroup(s). The average price of the selected agent is easy.

I cannot get the average price of his team(s) and his workgroup(s) working.

 

What it has to do is filter in the dimension agent > filter in the fact table > see his team(s) and workgroup(s) and take the average of that. If I select the team(s) and/or workgroup(s) manually in the dimension tables it works fine but I want Power BI to do that trick. 

 

I tried configuring a BOTH filtering on the join between my fact and the team dimension. If I do that the average price of the team(s) of a selected agent works perfectly (Using CALCULATE(price, ALL(agent_name)) DAX) but since I can only set one BOTH filter in my whole data model the average price of the workgroup(s) is not filted and thus wrong.

 

It must be possible because there is a custom slicer called HierachySlicer and if I use Agent Name and Team Name in that visual it automatically creates the hierarchy so if I select one team I only see the Agents of that team (through the fact table) and vice versa. 

 

I seriously hope somebody can help me, because we have a lot more Power BI dashboards to build with this structure.

 

Thanks!

 

Marco

 

1 ACCEPTED SOLUTION

@Anonymous see attached, hope this is what you are looking for, tweak it further as per your need, ignore the tables i added, it was just to confirm/debug for my purpose.



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.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@Anonymous yes it is possible, if you can share sample data in excel sheet wiht few records of each dim/facts and can get you the solution.

 

at end of the day, i guess you are looking for, if you select an agent in a slicer, you want to see his avg and also his team and workgroup avg, correct?

 

please share data in excel using onedrive/google drive link in the post.



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.

Anonymous
Not applicable

Hi,

 

https://drive.google.com/file/d/1Y43YSq3p5B0BNBOjKy4QpLBUKSpO366c/view?usp=sharing

 

I've created a sample dataset with 3 dimensions and one fact.

 

If you filter on an agent you will see his average and I also want to see his/her average per team and workgroup. 

 

I really hope you can help me!

 

Thanks in advance!!

 

Marco

@Anonymous one thing is not very clear, from your model, it looks like Agent can be part of multiple team and multiple workgroups, is this correct?

 

If that is the case, once you select an agent, which team/workgroup will be used of that agent to calculate avg price or you want to calculate the avg price of all the team/workgroup he is part of?



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.

Anonymous
Not applicable

My Hero!

 

Thank you very much this is what I wanted!

 

Wauw didn't know the variable way 🙂

 

I have another question that is actually related to my datamodel and that is filtering across slicers that are linked to different dimension tables. In my example I have Agent, Team and Workgroup.
What I want is that if I select an agent in my slicer, the Team and Workgroup slicers are filtered on the Teams and Workgroups that are linked to that selected Agent. 

 

If you are working with just 1 fact table you can select Cross filtering on all your dimensions and it works fine. But lets say you have multiple fact tables? Then you cannot configure cross filtering for you dimensions on all the joins to the fact tables.

 

I've added another Fact table to the example. Can you please have a look 🙂

 

https://drive.google.com/file/d/1Y43YSq3p5B0BNBOjKy4QpLBUKSpO366c/view?usp=sharing

 

Marco

Anonymous
Not applicable

"If that is the case, once you select an agent, which team/workgroup will be used of that agent to calculate avg price or you want to calculate the avg price of all the team/workgroup he is part of?"

 

i want to calculate the avg price of the teams he is part of and the average price of all the workgroups he is part of. 

@Anonymous see attached, hope this is what you are looking for, tweak it further as per your need, ignore the tables i added, it was just to confirm/debug for my purpose.



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.