One dimension two relationships how to use filters in fact table

[TheKnall]

Hi Guys

I have an issue that is more of an design and usage of filter dimensions with a fact table.



Here I have a Fact table called T_JOB. It contains two columns called LAST_PORT & NEXT_PORT This is two location id's. The Ports (Dimension) contians all the information about the location.


So I have create an active relationshiip between 'Ports (Dimension [Portcode]'  & Job_ID[Last_PORT]. It's a One to Many relationship.  Also have an Inactive relationship with 'Ports (Dimension [Portcode]'  & Job_ID[Next_PORT]



When  i now want to create a table with T_JOB[JOBID] and other elements from [Ports (Dimension) i am using the active relation, this works fine. But then I want to do the same thing with Next port (inactive) but of course it only shows the values from the active relationship.

Question:
What is the preffered method to deal with this situation?

Power Query:
1)We have tried merging in Query Editor, this works. Need to merge in all the values from Ports.
2)Create multiple tables, one for Next_port another for Last_Port.

DAX:
3) Have tried this Dax statement, that works but have to do this for evry value I want to show.

TEMP Last POrt = (CALCULATE(
(VALUES('Ports (Dimension)'[PortName]));
(USERELATIONSHIP(T_JOB[LAST_PORT];'Ports (Dimension)'[Port Code]))))


So far my conclution is that creating two dimensions from the same table is the easiest.
But have a feeling that this can be optimised. Would like to have all the filters in the Ports Dimension not both places.. 

Thx for all input on this

[v-huizhn-msft]

Hi @TheKnall,

For your DAX formula bemow, you create a calculated column or measure? It seems still use the active relationship rather than the one in USERELATIONSHIP.

TEMP Last POrt = (CALCULATE(
(VALUES('Ports (Dimension)'[PortName]));
(USERELATIONSHIP(T_JOB[LAST_PORT];'Ports (Dimension)'[Port Code]))))

I test it using a calculated column, I replace the part( 'Ports (Dimension)'[PortName])) a number type field, like min(Table[A]), it returns a error.

Best Regards,
Angelia

[TheKnall]

Hi @v-huizhn-msft

I create a calculated column

Yes that is true the Calculation will only work on the active link and not on the selected inactive link.

Used this (below) calculation also, worked better for me but, still not good result for the main question.

Last Port Name =

CALCULATE (

CALCULATE (

VALUES ( 'Ports (Dimension)'[PortName] );T_JOB);

USERELATIONSHIP ( T_JOB[LAST_PORT];'Ports (Dimension)'[Port Code]);

ALL ('Ports (Dimension)')

)

[v-huizhn-msft]

Hi @TheKnall,

The result is still uncorrect?

Regards,
Angelia

[TheKnall]

Well. My main question stillk stands. "What is the best approach, when working with filters.

Creating a calculated column, with the messure works, but requires  caluclation for every filter to be used.

Regards

TheKnall

---

@v-huizhn-msft wrote:

Hi @TheKnall,

The result is still uncorrect?

Regards,
Angelia

---

[v-huizhn-msft]

Hi @TheKnall,

Got it. I will post the update if I find better solution.

Best Regards,
Angelia

[CahabaData]

Your conclusion of creating a 2nd dimension table from the same table is the easiest - if one only needs 2.  It would not be a solution if there was a need for alot more.  In example - I come from the database/sql world where we can easily duplicate a table.  So for instance the Country table (list of countries) can be needed wherever there is an address.  In an sql statement we can have tables Country, Country1, Country2 - and dynamically define virtual copies of the same table to join each.

Right now in PBI one would need to make a calculated duplicate table, as you have concluded but this sits in the table model and making alot of them isn't feasible.  An alternative is to employ the method: USERRELATIONSHIP

If you look up that topic you'll find examples of it's use in dynamically selecting one join versus another.  In YouTube one good example is by Sam McKay Enterprise DNA New Zealand titled Handling Multiple Dates. 

Perhaps another community member has a suggestion on a better approach regarding this topic.  I know I would be interested to learn more on this.  Hope this helps a little.