Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

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

Syndicated - Outbound

@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

Syndicated - Outbound

@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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

@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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

@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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

@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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

@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.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)