CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Gabz
Gabz

Reputation: 97

PowerBI dynamic table with measures

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.

Upvotes: 1

Views: 5135

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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]
    )

Upvotes: 1

Related Questions