SUMMARIZE base on 2 tables, in error "he column 'SYSCred' specified in the 'SUMMARIZE' function was

[SunnyWu]

hi expert,

i am new to power BI, and when I try to use summarize function with 2 tables with following syntax, 

var t_op = SUMMARIZE(
MergeALLSQL,
MergeALLSQL[name1],
MergeALLSQL[re_FORM_OP1],
MergeALLSQL[Account],
JDT1op[SYSCred]
)

it shows the error "the column 'SYSCred' specified in the 'SUMMARIZE' function was not found in the input table."

tables are in many with many relationships, and mapping with the account number, 

any idea?

Sunny Wu

[tamerj1]

Hi @SunnyWu 

please try

var t_op =
GENERATE (
SUMMARIZE (
MergeALLSQL,
MergeALLSQL[name1],
MergeALLSQL[re_FORM_OP1],
MergeALLSQL[Account]
),
RELATEDTABLE ( VALUES ( JDT1op[SYSCred] ) )
)

[SunnyWu]

Hi @tamerj1 ,

Thanks you first, but it shows an error The RELATEDTABLE function expects a table reference for argument '1', but a table expression was used.

sunny

[tamerj1 · Accepted solution]

@SunnyWu 

Yes you are right. It was my mistake. Please try

var t_op =
GENERATE (
SUMMARIZE (
MergeALLSQL,
MergeALLSQL[name1],
MergeALLSQL[re_FORM_OP1],
MergeALLSQL[Account]
),
SELECTCOLUMNS ( RELATEDTABLE ( JDT1op ), "@SYSCred", JDT1op[SYSCred] )
)

[SunnyWu]

Thx, it is working, and it is inner join, many thanks!

[themistoklis]

@SunnyWu 

Try the following that creates a new table:

Table = 
var combinetable = ADDCOLUMNS('MergeALLSQL',"SYS Cred",RELATED('JDT1op'[SYSCred]))
return 
SUMMARIZE(combinetable,[SYS Cred],'MergeALLSQL'[name1],'MergeALLSQL'[re_FORM_OP1],'MergeALLSQL'[Account])

[SunnyWu]

still not working

or i cannot use related() in many-to-many relationships?

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

[themistoklis]

@SunnyWu 

make sure that the fieldname is written correcty in the formula and in the relationship between the two tables select 'Both' on 'Cross Filter Direction'

[SunnyWu]

hi @themistoklis i counter check all the field names

and 

the relationship is 'Both' on 'Cross Filter Direction'