Syntax for RELATEDTABLE to combine different tables

[Anonymous]

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?

[parry2k]

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

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

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])

 

[parry2k]

@Anonymous wwhat is the relationship between all these tables?

[Anonymous]

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]

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

[Anonymous]

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]

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