Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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
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
)
@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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |