Data Modelling issues

[Anonymous]

Hello guys, 


I am trying to solve a data modelling problem, let me explain the model below:

The tables: dOPS, fVendas, fFaturamento, TabelaAnoMesProduto, are related to fPrevisão with the key column "ChavePrev" which contains the code of the product, and concatenated with it, the month/year column. So it would be like (product code) 903-0000|05/2024. (month/year)

The fPrevisão table means, Sales Forecast, so for each month there is a forecast of the quantity of the product that the sales department defined to sell.  That's why the key column is the product with the quantity predicted for each month. (And I don't have another column to use as a primary key)

And here is where the problem lies. Around the fPrevisão table, the fact tables such as fFaturamento (Revenue), fVendas (Sales), dOPS (ProductionOrders) as mentioned before, are relating with the key " product | month/year " column.  And for filtering purposes, I am using the month/year column of the fPrevisão table to filter the date, it means that the products that are missing in this table and contains in the fact tables, will not be shown if I filter a period. 

These products market below, won't be shown in the filter context. 

I tried to unite them all by relating dates column with the dCalendar, which should be the main date filter of the report. But because of this specific relationship (image below) with dPrecoMedio (Avg Price Forecast); a table made on online excel by the sales department to use the avg price as a calculation to forecast revenue (avg price x quantity sold), I can't cancel the relationships between fPrevisão and those fact tables, because all of them use the avg price column for calculation purposes. So using dCalendar as a unique date couldn't be a solution I think. 

Another attempt I tried, was creating a intermediate table, that contains all the month/year and all the products, that is the TabelaAnoMesProduto (TableMonthYearProduct in english) 

This table contains all the combinations of product and month year, so I would use this as a filter in my report, and then I thought I wouldn't have problems filtering and have missing products of the fact tables. 

But then, after trying to filter using this new month/year column of the intermediate table, even so it didn't bring the values filtered.

Now I am out of ideas of how can I solve this problem, do you guys can help me out solving this? Any doubts I can detail more.

Thanks!

[Anonymous · Accepted solution]

Thank you for your answer! 

I managed to fix it by turning the intermediate table with unique values and after that make it as a bridge to the fact tables.

[Anonymous]

Hi, @Anonymous 

1.Firstly, please ensure that your dCalendar table (the dimension table) is comprehensive, including all possible month/year combinations for your products. You can use the Calendar() function or the Union() function to merge multiple tables, followed by the Distinct() function to remove duplicates.

 For further details, please refer to:

CALENDAR function (DAX) - DAX | Microsoft Learn
DISTINCT (table) function (DAX) - DAX | Microsoft Learn
UNION function (DAX) - DAX | Microsoft Learn
SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn
 

2.Secondly, you can also use the following measures for direct filtering:

Sales Measure = 
IF(
    ISBLANK(SUM(fVendas[SalesAmount])),
    0,
    SUM(fVendas[SalesAmount])
)

3.Finally, ensure that when using visualisations, you incorporate the dimension table to guarantee that all field values are included.

As we do not have specific data for practical testing, I can only suggest that you try a few adjustments incrementally. If you would like more specific assistance, I recommend uploading your PBIX file, ensuring that any sensitive information is removed. Currently, it would be advisable to upload it to GitHub.
 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.