Forum Discussion
DAX AddColumns
- 9 years ago
Looks to me like you have your ADDCOLUMNS in the wrong place. It looks like you are using it as a filter, rather than appending on a genuine column to the result
I'd say it should look more like this
EVALUATE ADDCOLUMNS ( ( CALCULATETABLE ( ETO, FILTER ( VALUES ( 'Company'[ParentCompanyCode] ), PATHCONTAINS ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( @CompanyCode, "{ ", "" ), " }", "" ), ",", "|" ), 'Company'[ParentCompanyCode] ) ), FILTER ( VALUES ( 'Company'[BrandID] ), PATHCONTAINS ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( @BrandID, "{ ", "" ), " }", "" ), ",", "|" ), 'Company'[BrandID] ) ), 'ETO'[Activity] = @Activity, 'Date'[DateValue] >= DATEVALUE ( @DateFrom ), 'Date'[DateValue] <= DATEVALUE ( @DateTo ), ) ) 'Company', "CompanyName", Company[Company] )I might have the brackets wrong as it's much easier to test with a dataset.
I'd expect to also have to use either RELATED() or RELATEDTABLE() to get the data to appear as well.
Looks to me like you have your ADDCOLUMNS in the wrong place. It looks like you are using it as a filter, rather than appending on a genuine column to the result
I'd say it should look more like this
EVALUATE
ADDCOLUMNS ( (
CALCULATETABLE (
ETO,
FILTER (
VALUES ( 'Company'[ParentCompanyCode] ),
PATHCONTAINS (
SUBSTITUTE (
SUBSTITUTE ( SUBSTITUTE ( @CompanyCode, "{ ", "" ), " }", "" ),
",",
"|"
),
'Company'[ParentCompanyCode]
)
),
FILTER (
VALUES ( 'Company'[BrandID] ),
PATHCONTAINS (
SUBSTITUTE (
SUBSTITUTE ( SUBSTITUTE ( @BrandID, "{ ", "" ), " }", "" ),
",",
"|"
),
'Company'[BrandID]
)
),
'ETO'[Activity] = @Activity,
'Date'[DateValue] >= DATEVALUE ( @DateFrom ),
'Date'[DateValue] <= DATEVALUE ( @DateTo ),
)
) 'Company', "CompanyName", Company[Company] )I might have the brackets wrong as it's much easier to test with a dataset.
I'd expect to also have to use either RELATED() or RELATEDTABLE() to get the data to appear as well.
- Anonymous9 years agoNot applicable
Thanks Phil,
The Related function was also required
evaluate ( topN(10,
ADDCOLUMNS( 'ETO', "CompanyName", RELATED ('Company'[Company] ) )
))
Cheers