topic Re: SUMMARIZE base on 2 tables, in error "he column 'SYSCred' specified in the 'SUMMARIZE' function was in DAX Commands and Tips

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

SunnyWu — Thu, 29 Dec 2022 15:57:06 GMT

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

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

tamerj1 — Thu, 29 Dec 2022 16:23:39 GMT

Hi SunnyWu

please try

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

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

SunnyWu — Thu, 29 Dec 2022 16:31:43 GMT

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

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

themistoklis — Thu, 29 Dec 2022 16:40:12 GMT

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

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

SunnyWu — Thu, 29 Dec 2022 16:53:29 GMT

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

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

themistoklis — Thu, 29 Dec 2022 17:17:40 GMT

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'

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

SunnyWu — Thu, 29 Dec 2022 17:30:01 GMT

hi themistoklis i counter check all the field names

and

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

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

tamerj1 — Thu, 29 Dec 2022 17:37:43 GMT

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

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

SunnyWu — Thu, 29 Dec 2022 18:16:59 GMT

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