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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
phistudio987
Regular Visitor

Refining Date Slicer for Multiple Table Filtering

Configuring the Date Slicer in Power BI to facilitate proper filtering in two tables presents a challenge in my current dataset, where the Company and Deals tables maintain a 1:N relationship. At present, I've implemented a Year and Month Slicer using the "Create Date" field from the Company table, successfully filtering the Deals table based on this field through matching connections.

 

However, my specific goal is to expand this filtering to the Company table, utilizing the "Create Date" field from the Company table and the associated "Close Date" field exclusively from the Deals table. To address this, I pursued the following approach:

 

1. I generated date entries covering the entire range between the minimum and maximum date values from the Company table's "Create Date" field.

2. This resulted in a single column of dates. Subsequently, I connected the Company table's "Create Date" field with the Date field, and similarly, I connected the Deals table's "Close Date" field with the Date field.

 

While I've followed this approach, I am unsure if it is the correct method. I had hoped for a more straightforward solution but have not found one yet.

 

I am seeking guidance to validate and enhance this approach, and I appreciate any assistance to ensure the date slicer seamlessly works across the desired data fields. Thank you for your support.

1 ACCEPTED SOLUTION

Hi @phistudio987 

 

Creating a Date table in the model and connecting it to fact tables is not the only way but it is an elegant way. As the date table has continuous and distinct dates in a date range, using it to filter other fact tables can avoid missing any date. If we use a date column from a fact table for filtering other tables, it might miss some dates if those dates are not existing in that fact table. 

 

Also using a date table has many other advantages, e.g. it can make time intelligence calculations easier. That's why we usually recommend having a date table. You may learn more from Do You Need a Date Dimension? - RADACAD

 

In addition, what you did actually implements the star schema model concept to some extent. Star schema is also recommended when modeling. 

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

Power BI – Star schema or single table - SQLBI

 

So be confident in what you have made. For refining it, you can connect the date table to the Deals table too. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

4 REPLIES 4
phistudio987
Regular Visitor

Thanks for the clarification!

amitchandak
Super User
Super User

@phistudio987 , to me seem like you need very similar approach like HR analytics , Active, Created and closed measures

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

 

 

 

Thank you for your suggestions. I think the solution you suggested is something I have tried already but that is not I was looking for.

Do you think that there is no other way to do this? I was trying to find some simple and elegant way to doing this rather than creating a separate Date table.

I wonder if creating Date table is the only way to doing this in Power BI.

Hi @phistudio987 

 

Creating a Date table in the model and connecting it to fact tables is not the only way but it is an elegant way. As the date table has continuous and distinct dates in a date range, using it to filter other fact tables can avoid missing any date. If we use a date column from a fact table for filtering other tables, it might miss some dates if those dates are not existing in that fact table. 

 

Also using a date table has many other advantages, e.g. it can make time intelligence calculations easier. That's why we usually recommend having a date table. You may learn more from Do You Need a Date Dimension? - RADACAD

 

In addition, what you did actually implements the star schema model concept to some extent. Star schema is also recommended when modeling. 

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

Power BI – Star schema or single table - SQLBI

 

So be confident in what you have made. For refining it, you can connect the date table to the Deals table too. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors