Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Syntax for RELATEDTABLE to combine different tables

Hi All,

I have two Tables as Client Table and Transaction Table. 

I want to create a new table using the columns from both the tables and then use SUMMARIZE function to achieve my requirement.

I have tried creating the below DAX:

NewTable =
VAR Combinetable = ADDCOLUMNS('Client',"TYear",RELATED('Transaction'[Transaction Year]))
Return
CALCULATETABLE(SUMMARIZE(
Combinetable,
Client[Vertical],
Client[Client Id],
Client[Client Name],
[TYear],
"Measure1",[Measure1],"Measure2",[Measure2]),
FILTER('Transaction',[Is Demo] = False)
)
The error I get is "The column 'Transaction[Transaction Year]' either doesn't exist or doesn't have a relationship to any table available in the current context."
In the Data Model, my relationship between the two tables is Many to One (from Transaction to Client) on Client Key (and Cross filter direction is Both).

Also, I have tried using the RELATEDTABLE function as
VAR Combinetable = ADDCOLUMNS('Client',"TYear",RELATEDTABLE('Transaction'),[Transaction Year])
The error says: "Function ADDCOLUMNS expects a column name as argument number 4."
 

How can I correct my DAX to achieve this requirement?



1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Anonymous try this:

 

SUMMARIZE(

FILTER(
'Transaction', 
'Transaction'[Is Test Order] = False && 
'Source System'[Name] = "Professional" &&
'Client'[Is Demo] = False &&
'Client'[Is Client Test Mode] = False &&
'Transaction Date'[Year] <> BLANK()
),
Client[Vertical],
Client[Client Id],
Client[Client Name],
'Transaction'[Transaction Year],
"Measure1" , [Measure1] , "Measure2",[Measure2])

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@Anonymous Well you have to check if filtering on all these conditions return any row, I cannot tell that.

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k ,
Thanks a lot for your help and time.
I have one more question here what if I need to get Transaction Date [Year] instead of Transaction [Year]. The Transaction Date table has no relation with Client Table but Transaction to Transaction Date relationship is Many to One.
How can I make the below query working?

SUMMARIZE(
FILTER('Transaction Date',
'Transaction Date'[Year] <> BLANK() &&
SELECTEDVALUE('Transaction'[Is Imported]) = False &&
SELECTEDVALUE('Transaction'[Is Test Order]) = False
&& SELECTEDVALUE('Client'[Is Demo]) = False
&& SELECTEDVALUE('Client'[Is Client Test Mode]) = False
),
'Transaction Date'[Year],
Client[Vertical],
Client[Client Id],
Client[Instance Name], "Measure1",[Measure1],"Measure2",[Measure2])
parry2k
Super User
Super User

@Anonymous try this:

 

SUMMARIZE(

FILTER(
'Transaction', 
'Transaction'[Is Test Order] = False && 
'Source System'[Name] = "Professional" &&
'Client'[Is Demo] = False &&
'Client'[Is Client Test Mode] = False &&
'Transaction Date'[Year] <> BLANK()
),
Client[Vertical],
Client[Client Id],
Client[Client Name],
'Transaction'[Transaction Year],
"Measure1" , [Measure1] , "Measure2",[Measure2])

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k,
I have tried in that way, but for some reason, there are no values in the table now.

SUMMARIZE(
FILTER('Transaction',
'Transaction'[Is Imported] = False && 'Transaction'[Is Test Order] = False &&
SELECTEDVALUE('Source System'[Name]) = "Professional" &&
SELECTEDVALUE('Client'[Is Demo]) = False &&
SELECTEDVALUE('Client'[Is Client Test Mode]) = False &&
SELECTEDVALUE('Transaction Date'[Year]) <> BLANK()
),
Client[Vertical],
Client[Client Id],
Client[Instance Name],
'Transaction'[Transaction Year],"Measure1",[Measure1],"Measure2",[Measure2])
SaloniGupta_0-1633406033304.png

 

 
parry2k
Super User
Super User

@Anonymous wwhat is the relationship between all these tables?

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k ,
Relationships amongst these tables:
Transaction to Source System (Many to One),
Transaction to Client (Many to One) (Cross Filter= Both),
Transaction to Transaction Date (Many to One)

parry2k
Super User
Super User

@Anonymous I think you need this if you want data for each  year of the client:

 

New Table = 
SUMMARIZE(
FILTER('Transaction',[Is Demo] = False),
Client[Vertical],
Client[Client Id],
Client[Client Name],
'Transaction'[TYear],
"Measure1",[Measure1],
"Measure2",[Measure2])
)

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi @parry2k ,
Thanks for replying.
Apologies I did not mention it earlier but I do have other filters as well in my DAX as below:

SUMMARIZE(
FILTER('Source System',[Name] = "Professional"),
FILTER('Client',[Is Demo] = False && [Is Client Test Mode] = False),
FILTER('Transaction', [Is Test Order] = False),
FILTER('Transaction Date',[Year] <> BLANK()),
Client[Vertical],
Client[Client Id],
Client[Client Name],
'Transaction'[Transaction Year],
"Measure1" , [Measure1] , "Measure2",[Measure2])

This gives me an error saying: "Function SUMMARIZE expects a column name as argument number 2."
parry2k
Super User
Super User

@Anonymous I believe Transaction table is on the many side of the relationship and if that is the case then you cannot use RELATED



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.