Two fields in fact table to the same dimension table

[Anonymous]

Good day everyone,

I'm having trouble modelling this. I have this fact table with two columns, one each for a voyage;

1) The voyage to our warehouse

2) The voyage from our warehouse 

Now I got this dimension table with all voyages in the same column.

How do I shape this into a star schema, so that all my relationships stay active? I've been thinking about making two bridge tables, one for each type of voyage. And then link those to my dimension. But that seems kind of weird way to solve this.

Thanks!

[v-yangliu-msft]

Hi  @Anonymous ,

You may consider using Lookupvalue() function to search the desired column.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Best Regards,

Liu Yang

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

[Maulik_Domadia]

Hello,

You can try UserRelationship dax or you can create a key in Fact Table and Dim table then you can probably use to create Star Schema. 

Can you please share a demo file which covers your case?

[bcdobbs]

What is the grain of your fact table. Eg what does one row represent?

[Anonymous]

Hi @bcdobbs ,

One row in my fact table represents an entire trip (transport), so get goods somewhere, to our warehouse, and deliver them somewhere else. That's why I have both the voyage_in and voyage_out on the same row.

What would you suggest I do?

Thanks

[bcdobbs]

I'd probably have two dimensions which could essentially be copies of each other. One being an origin dimension and another being a destination.

You then effectively have a factless fact table that describes transit between origin and destination. 

As @Maulik_Domadia said you could leave it

from one dimension and use USERELATIONSHIP in a measure but then you're moving a lot of work to dax rather than having a clear model.