Forum Discussion
summarize columns using related dimension table
It seems that summarizecolumns doesn't respect relationships between a fact table and a date dimension table? Is this true?
For example:
If I do the following in DAX:
New Table = SUMMARIZECOLUMNS(
'Shipment Line'[CreatedDate],
'Date'[YearQuarter],
FILTER('Product','Product'[Product Group.Name] = "Solar Panels")
)
I would expect the resultant table to to show the appropriate "YearQuarter" relative to the "CreatedDate" (Fact table), however, it doesn't "join" as expected. The result looks like:
However, in diagramming view, the relationship is respected:
Any ideas here?
I should point out that it appears relationships between other fact tables seems to hold using summarizecolumns. It's just the dax date dimension table that fails.
In your DAX, you don’t include any expression in the SUMMARIZECOLUMNS function, thus you obtain a crossjoin of Shipment Line table and Date table as a result, and the formula you use will produces all the possible combinations between CreatedDate and YearQuarter.
Create a new table by adding expression in the SUMMARIZECOLUMNS function, there is an example for your reference.
New Table = SUMMARIZECOLUMNS( 'Calendar'[Year], Sales[DateKey], FILTER('Product','Product'[BrandName]="Contoso"),"total sales",SUM(Sales[SalesAmount] ))Please review the following blogs about how to use SUMMARIZECOLUMNS function.
https://www.sqlbi.com/articles/introducing-summarizecolumns/
http://umashanthan.blogspot.sg/2017/04/how-to-use-summarizecolumns-function.html
Regards,
Lydia
1 Reply
In your DAX, you don’t include any expression in the SUMMARIZECOLUMNS function, thus you obtain a crossjoin of Shipment Line table and Date table as a result, and the formula you use will produces all the possible combinations between CreatedDate and YearQuarter.
Create a new table by adding expression in the SUMMARIZECOLUMNS function, there is an example for your reference.
New Table = SUMMARIZECOLUMNS( 'Calendar'[Year], Sales[DateKey], FILTER('Product','Product'[BrandName]="Contoso"),"total sales",SUM(Sales[SalesAmount] ))Please review the following blogs about how to use SUMMARIZECOLUMNS function.
https://www.sqlbi.com/articles/introducing-summarizecolumns/
http://umashanthan.blogspot.sg/2017/04/how-to-use-summarizecolumns-function.html
Regards,
Lydia