topic Re: How to override the row level context. in DAX Commands and Tips

How to override the row level context.

Chetna_Rath — Thu, 25 Aug 2022 08:07:58 GMT

I have two Heirarchies, unable to find the sum by category:

I have data something like this:

I need the final table as below:

Re: How to override the row level context.

Jihwan_Kim — Thu, 25 Aug 2022 08:40:38 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = SUMMARIZE ( ADDCOLUMNS ( Data, "@Sum by Category", SUMX ( FILTER ( Data, Data[Category] = EARLIER ( Data[Category] ) ), Data[Sales] ) ), Data[Sub Category], Data[Sales], [@Sum by Category] )

Re: How to override the row level context.

Chetna_Rath — Thu, 25 Aug 2022 09:13:00 GMT

Hey. Thanks for the solution, but what if the sales column is in a different table?

Re: How to override the row level context.

Jihwan_Kim — Thu, 25 Aug 2022 09:54:18 GMT

Hi,

Thank you for your feedback.

Could you please describe how the two tables look like?

Re: How to override the row level context.

Chetna_Rath — Thu, 25 Aug 2022 10:02:13 GMT

Hi,

If we had a dimension table - Product

And another fact table containing other KPIs like sales.

Re: How to override the row level context.

Jihwan_Kim — Thu, 25 Aug 2022 17:29:31 GMT

Hi,

Thank you for your message.

Please check the below picture and the attached pbix file.

It is for creating a new table.

New table = SUMMARIZE ( ADDCOLUMNS ( 'Product', "@Sales_Subcategory", SUMX ( RELATEDTABLE ( 'Fact' ), 'Fact'[Sales] ), "@Sales_Category", SUMX ( CALCULATETABLE ( RELATEDTABLE ( 'Fact' ), FILTER ( ALL ( 'Product' ), 'Product'[Category] = EARLIER ( 'Product'[Category] ) ) ), 'Fact'[Sales] ) ), 'Product'[Sub Category], [@Sales_Subcategory], [@Sales_Category] )