Building Relationships on multiple tables with no unique values

[jwasilko]

I have a report with a FactSales table and 2 dimensional tables (customer and product). 

My question pertains to my product dimesional table, which has columns such as the product number, product line, and segment/category (this one is important). The value that is unique in this table is the product number and this is the value that is creating a relationship between my dimensional and fact table. Many products can be apart of each segment (like rings and necklaces are both jewlery), menaing the segment column has repeated/duplicated values in it. 

I am trying to incorporate the budget into my report, but the data source for the budget is different than the FactSales table. I have 2 excel sheets that I am importing into Power Bi, which are creating 2 different tables. One with my forecasted (future) sales budget and one with my actual sales budget. Both budget files have columns by segment and the period (month and year) the budget is for. Also with repeating values in the columns, as there is a budget for each segment and the corresponding period. (I guess you would consider these fact tables?)

In my report, I have a slicer that is filtering visuals by the segment, but when I include the budget numbers in a graph with values from my FactSales table, the slicer has no effect on the budget results because there are no unique values in my budget tables (other than the budget price), and I cannot form a relationship between my budget and product dimesnion tables. 

Is there a way I should be formating my budget tables or a DAX measure to create the relationship I am looking for, so that slicers are being applies to all tables? 

Here is a picture of my data model for reference: 

 

[v-tangjie-msft]

Hi @jwasilko ,

You can consider creating a separate subdivision table as one end table and other tables to create model relationships, or if you have multiple relationships, you can use the USERELATIONSHIP() function to activate inactive model relationships.

For more details, please refer: Financial Statement Analysis with Power BI - Microsoft Fabric Community

Financial Modeling in Power BI: Templates & Insights | Inforiver

Best Regards,

Neeko Tang

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

[jwasilko]

The things is though, I don't have multiple relationships within my model. Each of my tables have one relationship with another. But I'm struggling to form any kind of relationship between my budget tables and my product dimension because there are no unique values. My slicers aren't effective due to no relationship being formed. Is there a DAX measure I can implement to help?

[v-tangjie-msft · Accepted solution]

Hi @jwasilko ,

You mean to take the Segment field of the Forecast table as a slicer, filter the fact table, right? You can create a measure and then place [Measure=1] on the filtering of the visual.

Measure = IF(MAX('Fact sales'[Segment]) in VALUES('Forecast'[segment]),1,0)

Best Regards,

Neeko Tang

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