Using inactive relationship in visuals

[Stel]

Hi all,

I have a question about inactive relationships. I have following tables:
- fact_order with:

- 1 active relationship to dim_customer
- 1 active relationship to dim_date (order_date) and 1 inactive relationship to dim_date (ship_date)
- 1 active relationship to dim_product

- fact_picking with:

- 1 active relationship to dim_date (pick_date)
- 1 active relationship to dim_product
- 1 inactive relationship to fact_order => I can't put this one active because I get an ambiguity error on column dim_product. I've tried setting that relationship inactive but then
I've got the same error message with the dim_date column.

I was wondering if the current way the relationships are set up is fine?
Because I can't set the relationship between fact_order and fact_picking active, I've encountered following problems:

- I needed to create a calculated measure for a visualisation of picks by customer to be able to specify the 'userelationship'. Problem with using calculated measures is that I can't see
the records in detail anymore if I want to dig deeper
- I want to have a table visualisation that consist of fact_picking[quantity_picked], fact_order[order_id], dim_customer[customer_name]. Here I get the error that it's unable to show the data because there's no (active)
relationship between those columns.

Can anyone give me some pointers to how to fix the issues?

Thanks in advance

[v-kelly-msft]

Hi @Stel ，

Below references may help:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

If the above doesnt help,better show me the screenshot of your relationship model.I'll try to work it out.

Best Regards,
Kelly

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

[Stel]

Hi,

This is my datamodel:

[Stel]

@v-kelly-msft  or @Greg_Deckler Is it possible to have a look at the model and give me some advice in the active/inactive relationships I need to make between the tables? Or other suggestions?

Much appreciated

[Greg_Deckler]

@Stel In general I agree with @Anonymous , that model is uh, uh, ugly. Seems like you have a number of fact tables related to orders. I would assume that there should be some common id between them like order id. So, you might consider having a distinct list of order id's in a separate dimension table that you could use to tie all of them together. You can create this in Power Query or in DAX it is Table = DISTINCT('Table'[Order ID]) for example.

You can use inactive relationships in measure calculations by using the USERELATIONSHIP function in DAX but if you find yourself having to do that, 99 times out of 100 you have a flaw in your data model. Not always, but generally.

[Stel]

Hi,

I did what you suggested. Hope this makes it less ugly 🙂

Maybe to give you more context: the fact_picking table actually holds data about who has picked what article from which location, from which pallet, to which location and to which pallet. You may call it the actual picking trace

The fact_outboundorder table contains who is the customer, what did he order. Like 1 orderline of 10 items can result in 5 fact_picking lines when an employee, picks 5x2 pieces of the product

I get the point that I should use a star diagram and not connect 2 fact tables.

But now I want for my actual pick traces, slicing on customer. Currently I only have a reference of the customer in my fact_outboundorder table. 

@Greg_Deckler , @v-kelly-msft What is the best way to do that?

I've tried with a dim_outboundorder table as suggested to connect the 2 fact_tables but it's not working.

Thanks in advance.

[Anonymous]

@Stel, I can tell you right away that the model is faulty. Direct connections between fact tables should never be created. Unless... you want to face troubles later on. Please normalize the model correctly: fact tables must only ever be connected to dimensions. Think about which piece of info should belong to which table. This is of utmost importance to produce predictable numbers.

Here's an excerpt from an article by Alberto Ferrari:

The golden rule of data modeling is always the same: always use star schemas. If a column has to be used to slice and dice, then it needs to belong to a dimension. Numbers to aggregate, on the other hand, are stored in fact tables. Tabular lets a developer deviate from the regular star schema architecture. This does not mean that doing it is always a good idea. It seldom is.

And please remember forever: NEVER JOIN 2 FACT TABLES DIRECTLY.

Best

D

[Greg_Deckler]

Can you post an image of your data model? Easier to visualize that way.