Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duybachhpvn
Frequent Visitor

Dynamic virtual summarized table

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.

duybachhpvn_0-1643610914390.png

 

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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.

vchenwuzmsft_0-1643964511146.png

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:

vchenwuzmsft_2-1643964686231.png

 

 

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.

 

View solution in original post

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

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.

vchenwuzmsft_0-1643964511146.png

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:

vchenwuzmsft_2-1643964686231.png

 

 

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.

 

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]

 

duybachhpvn_0-1643965817920.png

 

 

 

 

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:

duybachhpvn_1-1643966325392.png

 

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
)
    

 

 

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors