cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maciek_1000
Frequent Visitor

How to filter over date without reffering to a date table

Hi,

I've got a dimension table dim_projects. There is a list of projects with names and dates when the project was set. This table is connected with other dimension tables, which are finally connected with some fact tables and master date table.

The case is that I need to show next to i.e. sales sliced by quarters (fact_sale table is connected with the master date table), the number of the projects signed in every quarter. In my opinion, it is impossible to connect the dim_projects table with the master date table because it would create some ambiguity. How can I manage this case to be able to count finally the number of projects signed for each quarter?

Maciek_1000_1-1622997673361.png

 

 

1 ACCEPTED SOLUTION

@Nathaniel_Cat this moment I've decided to make a copy of the request of Project Table in Power Query. That has provided me the option to join the new table with dates and to count the number of projects signed in demanded periods. I'm not sure if this is a professional solution but it works fine. Maybe @MattAllington who you mentioned can provide a more elegant solution.

Thank you for your interest in the subject!

View solution in original post

4 REPLIES 4
Nathaniel_C
Super User I
Super User I

Hi @Maciek_1000 , Based on your description, your Projects Table is connected to your Sales table, right? If so, just like you create a measure like Total Sales = SUM(SalesFact[Sales]), create a measure on the column that captures the signed project. Total Signed Project = Sum(SalesFact[SignedProject].  Drop the calendar value by quarter, then the two measures in the visual. Hopefully I understood your question.

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C,

Thank you for your suggestion. The model is more complicated than I described. The Project table is associated with the forecast table and the forecast table is connected to the sales table (so there is another table between them).
Even if there was no table between project and sales table I couldn't count project id from the sales table. That is because there is no correlation between the signing date and the date when is sale.

Information about the signed date is in Project Table. However, sales are in Sales Table (and it is sliced quarterly). It means that I need somehow to connect dates and assign them to the Project table (but I can't connect the Project table to the date table directly). I think I need to make some additional construction but I can't imagine how?

 

I attach the picture. The orange is Project list table, the blue one is a fact sales table (actually forecasted, but it doesn't matter). 

Maciek_1000_0-1623051757985.png

 

 

Maciek

Hi @Maciek_1000 , yes you are right.  Your current setup does have ambiguity if you trace the path from project to forecast.  Let's see if @MattAllington is online.  
@MattAllington  who wrote Supercharge Power BI is really good with these type of inquiries!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Nathaniel_Cat this moment I've decided to make a copy of the request of Project Table in Power Query. That has provided me the option to join the new table with dates and to count the number of projects signed in demanded periods. I'm not sure if this is a professional solution but it works fine. Maybe @MattAllington who you mentioned can provide a more elegant solution.

Thank you for your interest in the subject!

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors