How to give Virtual Relationship between two tables in Power BI?
Do you want to build Virtual Relationship between two table in Microsoft Power BI?
Firstly, Ultimate aim of the post is not to show virtual Relational between two tables where we can create relationship in Power BI Data Modelling Pane. We created this blog is to show How to can using DAX Function to create relationship in DAX using TREATAS() DAX Function.
What is TREATAS Function?
TREATAS which is spells like TREAT-AS = "TREATAS"
TREATAS Function takes Expressions and Column as the Input Parameter
TREATAS(Expression 1, Column Name1, Column Name 2... Column Name N)
TREATAS Function treats column of Input Table as a column of Another Table.
In TREATAS Function, Expression 1 will returns tables.
AforAnalytics comes up with blog content based on their real time problem with their client.
We have faced similar scenario with one of our Real Estate client on Building Reports in Power BI. Here Let's see the consideration of Use case How we can do this with TREATAS() Function
We have a Data Model in Power BI that shows us Two Tables Tloan And TloanDeal where these Table doesn't have relationship in common.
If we try to build a Simple Table in Power BI to show Deal Names from TloanDeal and Commitment Amount from Tloan Table, you will see the below result.
All the Deal names from TLoandeal shows same Commitment Amount, this is because there is no relationship between two tables.
Let's Create a DAX function which contain TREATAS function which links TloanDeal and TLoan Table.
Commitment Amount for Loan Deals = CALCULATE( SUM(TLoan[Commitment Amount]), TREATAS( VALUES(TLoanDeal[Mandate ID ]),TLoan[Mandate ID ]) )
We are Using VALUE() in above DAX because The Expression Part in TREATAS() should return Table, it should return Column.
Once we add Measure into the Table, we will see below Result
Now, You can our New Measure with TREATAS() Function filtering out the values and creating virtual relationship between two tables using MandateID Column as a Key.
You are the valuable Reader of AforAnalytics Blog so We have planned to provide you a TWO Bonus
We are providing you another Post that will helps you to Understand the Alternative Approach for Passing Input parameter from Power BI Report
We are providing you the special Offer for One-One Power BI Training Program that AforAnalytics provides only for Corporate Training. If you want to sign up with the specific course, click here to sign up and get call from Trainer
Thanks for RADCAD to post similar Resources and help us to simplify the post.
Every Tool in Business Intelligence space has limitation in some way
AforAnalytics is a kind of company provide Innovative, Optimistic Solution as a Consulting service to provide solution in very less time for Tableau consulting service, Power BI Consulting service, Power Platform Consulting services, Machine Learning Consulting Service.
What do you think? Drop your comments below!