PowerBI dynamic table with measures

Question

PBI Newbie here. I'm trying to create a dynamic dable using the following syntax:

Test = {
("Measure 1", 'StandardKPIs'[Current measure1 value], 'StandardKPIs'[Previous measure1 value]),
("Measure 2", 'StandardKPIs'[Current measure1 value], 'StandardKPIs'[Previous measure2 value])
}

StandardKPIs is a table of Measures, and contains my KPIs. I basically want to load these into a table, so by the expression above, I was hoping to get a return of the following:

Value1	Value2	Value3
Measure1	12332	32222
Measure2	988	332

I also would like the values to of course update to reflect changes in report filters.

But I do see the "Value1" populated (which is the static string), but Value2 and Value3 appear static and do not take into account any filters that were applied on the report.

I have read in other forum posts that the values cannot update dynamically in this case because PBI loads the table only when it is initialized. I am hoping there is another way to achieve this functionality?

Any help will be appreciated.

using "SAMEPERIODLASTYEAR" function: CALCULATE([Current measure1 value], SAMEPERIODLASTYEAR(DimCalendar[Date])) — Gabz

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-14T18:10:44

You could create a table for the structure you want and then use a switching measure to fill the cells.

For example, NewTable =

Measure	Period
Measure1	Current
Measure1	Previous
Measure2	Current
Measure2	Previous

Then use the Measure column of this NewTable on the rows of a matrix visual and Period on the columns.

Then for the value field, create a measure that detects which cell it's in and returns the appropriate calculation:

VariableMeasure = 
VAR M = SELECTEDVALUE ( NewTable[Measure] )
VAR P = SELECTEDVALUE ( NewTable[Period] )
RETURN
    SWITCH (
        TRUE(),
        M = "Measure1" && P = "Current", [Current measure1 value],
        M = "Measure1" && P = "Previous", [Previous measure1 value],
        M = "Measure2" && P = "Current", [Current measure2 value],
        M = "Measure2" && P = "Previous", [Previous measure2 value]
    )

PowerBI dynamic table with measures

1 Answers