Relationship

Hi Anonymous ,

 

There are few main points for relationships in Power BI:

1. Relationships allow you to use data from mutiple tables for reporting, rather than having a single big table.
2. Relationship help in cross-filtering data among the tables where this is created.
3. In order to create a relationship, you should have a common column in both the tables.
4. Cardinality of a relationship can be one-one, one-many, many-one or many-many.
5. Many-many cardinality is generally avoided as it creates ambiguity in the data.

There are few articles on Power BI relationship which you can start with to understand them in detail:

https://radacad.com/back-to-basics-power-bi-relationship-demystified#:~:text=To%20create%20a%20relationship%20between,field%20on%20the%20other%20table.

https://radacad.com/what-is-the-direction-of-relationship-in-power-bi

 

Start with these, I am sure you will understand what these are and how they are created.

 

Thanks,

Pragati

Anonymous

Great question, I have been meaning to write a blog post on this topic for a while. I'm sure you've seen a few articles and videos out there from a google search.

Basically, your main data table is your FACT or transaction table - this has one row for each transaction (a transaction could be a sale, incident, complaint, registration, etc).

Let's use incident reporting as an example, since it differs a bit from the typical sales examples already posted on the internet and I'm not sure what your normal scenario is.

So if you are reporting on incidents that have been reported over the past month, you might see a table with columns such as:
* IncidentID
* IncidentDate
* EmployeeID
* TransactionID

From that small amount of information, I can use the Dimensional (demographic/static) data that I already have, to find:
* Month
* Employee name, gender, age
* Incident type, severity

So if we focus on the employee side of this, I only need to pull in data for each employee ONCE, to save space in my data model. The EmployeeID connects this data to every incident that employee reports, which may be MANY over the course of a month or year.

And that is our one to many relationship from Employee to Incidents.

I suggest using a SINGLE cross-filter direction as the default for 1 to many relationships, otherwise you'll get into tricky situations with circular relationships and funny data numbers.

Not all data models are so straightforward though, so feel free to come back with any other info/questions and I'll try to explain as best as I can, and add them to my examples for when I finally get around to writing that blog post. 🙂

Anonymous ,

refer

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

https://www.youtube.com/watch?v=-4ybWQSRcOY

https://www.youtube.com/watch?v=Zlu99RUtMRY

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

https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi