Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Alexis (Lex) Kinter]

So I’m an MS Access user, and am trying to understand what the Model is for when I can basically query and join using Merge or USERELATIONSHIP. Also (maybe related) I don’t understand “Cross Filter Direction”. Any suggested reading and/or thoughts?

Thank you in advance.

Lex

——————————
Alexis (Lex) Kinter
QA Engineer / Innovation Lead
——————————

[Cekou C.]

Hi Alex,

RELATED

You can think of RELATED as being as a VLOOKUP in Excel.
It allows you to access a column located in a different, but still related table. 
A typical example of this is, when you have a fact table with quantity sold but not the unit price which is in the product table. You can use RELATED to access the unit price of the product table for each row in the sales fact table.

USERELATIONSHIP

In Power BI, only one relationship between two tables can be active at the same time. If you need to link two tables by more than one relationship, then only one will be active and the others will be inactive. But this inactive relationship being created then can be activated by a DAX formula using USERELATIONSHIP.
A typical example of this is where you have a sales fact table with two date columns : [Order Date] and [Delivery Date]. Unless you have two date tables, you won’t be able to link both of them to the unique date table. In this case, one of the two will be active and the other inactive.

CROSSFILTER
The standard setting of a 1-to-many relationship in Power BI is that filters only flow from the “1” side (dimension table) to the “many” side of the relationship. 
If you need the filter to flow from both directions, then you can use the CROSSFILTER function to achieve this purpose.

Hopefully, this is clear.

Kr,

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Christopher Schnaars]

This is the best, most concise explanation of the differences between RELATED, USERELATIONSHIP and CROSSFILTER that I’ve ever seen. Thanks!

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Alexis (Lex) Kinter]

Thank you, Cekou. I’m not sure I’m understanding. Maybe you can help me with an example? I have two tables tblA and tblB. tblA is a data dump from another system.  tblB is a table I created to store additional data not provided in the other system. The updates to these two tables will occur at different times (tblA about weekly and tblB whenever my user wants to perform an update). Each table has a UpdatedDate field representing the date the data was updated.The two tables are uniquely related via two keys Job (data like “11111”) and Phase (data like “CON”). Right now, I want to join on the two keys (or composite key), allow the user to filter on the tblA.UpdatedDate,  and display the record from tblB using the tblB.UpdatedDate that is the most recent to tblA.UpdatedDate? I can give some sample data if needed. Thank you for any thoughts you may have. 

——————————
Alexis (Lex) Kinter
QA Engineer / Innovation Lead
——————————
——————————————-

[Cekou C.]

Hi Alexis,

Is tblA a dimension table with only unique values of [Job] and [Phase]?

What you want to achieve is clearly possible. However, the way to achieve this will depend on the initial relationship setting. (1-to-Many or Many-to-Many, single or bi-directional filters etc.)

Yes, a sample data may come in handy as well.

Kr,

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Alexis (Lex) Kinter]

Thanks again Cekou and sorry for taking so long to reply.

I don’t think of “Dimension” and “Fact” tables as I don’t have much experience in Pbi. From what I can tell, the discussion of the Star schema (and the Model) has to do with Performance. That is, if I DON’T structure in this way, the User may take a performance hit. I also want to deliver a product, and if the amount of data doesn’t lend need, I wouldn’t want to go through that effort. Do you know what amount of data lends need to restructure?

Also, wrt the unique value…I’m rethinking it. I’m creating the interface entrypoint for (as said above) tblB (additional data) and I could setup tblA M:M tblB on entry (that is, adding junction table in Sharepoint) rather than having to query it out. BUT, I know (as you said) it can be done within Pbi.

I’ve *attempted* upload of image to show relationship and sample data. The data is stored in Sharepoint. I’d like to better understand how to do this querying within Pbi, so if you have thoughts, please share. Thank you so much.

——————————
Alexis (Lex) Kinter
QA Engineer / Innovation Lead
——————————
——————————————-