Forum Discussion

duybachhpvn's avatar
duybachhpvn
Frequent Visitor
4 years ago
Solved

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 virtua...
DAX
dynamic table
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

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.

 

duybachhpvn's avatar
duybachhpvn
Frequent Visitor
4 years ago

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
)