topic Re: Dynamic virtual summarized table in DAX Commands and Tips

Dynamic virtual summarized table

duybachhpvn — Mon, 31 Jan 2022 06:36:03 GMT

Hi, is there a way to write a DAX measure to obtain a dynamic virtual table that will give me the desire result as in below scenario?

Basically the result I would want is the final Dynamic virtual table from rows 21 to 28, which can be dynamically filtered accordingly to visual filter context. If I have a visual (lets say a matrix table), and at one cell in that visual is Period 2022-Q1, I can have a table variable that changes accordingly to current filter context (which means I get rows 22 to 24) in that filter context.

I tried to use SUMMARIZE, but it does not allow me to bring in columns from 2 different tables in 1 measure. I also tried SUMMARIZECOLUMNS but it cannot result in a dynamic virtual table that will change according to the filter context within the visual.

Re: Dynamic virtual summarized table

amitchandak — Mon, 31 Jan 2022 07:02:03 GMT

duybachhpvn , You can not create a dynamic calculated table. Tables once created will be static. So your measures will not change based on slicer values

If you want add measures of another tables try like, in var of measures (that will be dynamic

measure ,

var _tab = addcolumns(summarize(Table, 'Common Dim1'[Column], , 'Common Dim2'[Column] ), "_1", [Measure1]

,"_2", [Meausre 2] )

You can add columns in summarize or addcolumns as per need

Re: Dynamic virtual summarized table

v-chenwuz-msft — Fri, 04 Feb 2022 08:52:04 GMT

Hi duybachhpvn ,

The tables we create (new tables) are static and cannot be created dynamically.

But SUMMARIZE can create a dynamic virtual table from two table, but the table can not be return. Let me prove it to you.
Before doing so, create a relationship between these tables.

I create a measure to return which is the result of summarize.

SUMMARIZE TABLE = var _1 = SUMMARIZE('Cost',[PeriodName],[ScenarloName],[Cost]) return CONCATENATEX(_1,[PeriodName] &" "&[ScenarloName]&" "&[Cost]," ")

result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Dynamic virtual summarized table

duybachhpvn — Mon, 07 Feb 2022 00:41:34 GMT

Hi v-chenwuz-msft

Thank you, that is very close to what I wanted. I made some changes to your DAX to make the summarize table become more dynamic inside a SUMX function as below (I added in a simple volume table). The measure below will give me dynamic calculation of total cost based on the filter context from

Periods[PeriodName] and Scenarlo[ScenarloName]

SUMMARIZE TABLE 2 = SUMX( SUMMARIZE( 'Cost', Periods[PeriodName], Scenarlo[ScenarloName], "Amount", VAR _Cost = SUM(Cost[Cost]) VAR _Volume = SUM(Volume[Volume]) RETURN _Cost * _Volume ), [Amount] )

result:

My question is whether the above DAX code has any issue? Because it is summarizing based on "Cost" table, but Im bringing in data from Volume table, so Im not sure if Im doing the right thing?

My initial thought was to have the dynamic Summarized table something similar to the SUMMARIZECOLUMNS, that I'm not summarizing any particular table, but bringing in fields from dimensions tables only to create the measure. The SUMMARIZECOLUMN static table would look something like below. But it does not work with SUMMARIZE function because SUMMARIZE needs to have first argument as a table.

SUMMARIZECOLUMNS table = SUMMARIZECOLUMNS( Scenarlo[ScenarloName], Periods[PeriodName], "Total Amount", VAR _Cost = SUM(Cost[Cost]) VAR _Volume = SUM(Volume[Volume]) RETURN _Cost * _Volume )