[DAX] Dynamic Table and Summarize Table

[acanepa]

Hello,

I'm using the following formula for generating a dynamic measure, creating a table (Slicer Table) on the side with each id and measure name. I have done this several times and works great.

switch(true,
max(id)=1,
[Measure 1],
blank()
)

The problem I'm facing right now is how to bypass that dynamic measure on a table on the fly.

For example

Measure 1=

var t1 = SUMMARIZE('Calendar',[Month],"v1",[Dynamic Measure])

RETURN(
AVERAGEX(t1,[v1])
)

At the time the measures make the calculations only consider the last ID for the Slicer table. If I add a filter on top of the measure, it works but only giving the concrete number

Measure 1=

var t1 = SUMMARIZE('Calendar',[Month],"v1",CALCULATE([Dynamic Measure],[ID]=2)

RETURN(
AVERAGEX(t1,[v1])
)

But if I change the number 2 to something like  MAX(ID) it would not recognize.

So I don't know how to pass the filters into the table on the fly for having direct connection to the slicer in the report.

[v-jiascu-msft]

Hi @acanepa,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[acanepa · Accepted solution]

Hi @v-jiascu-msft,

As I posted earlier my problem was around to generate a dynamic measure inside a table.

The solution I found rely on making a SWITCH operator with a generation of a table in each measure.

I was trying to calculate the average YOY for 2017*. Because is a measure is necessary to create a table to eliminate "row context".

The following calculation relies on Slicer table with the measures Traffic, Clicks and Users. The beauty of this you will need only one measure for display all measures at the same time and all measures share the same format. 

Calculation=

SWITCH(TRUE,
max(id)=1,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Traffic]),
[yoy]),
max(id)=2,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Clicks]),
[yoy]),
max(id)=3,
AVERAGEX(
SUMMARIZE(Calendar,'Calendar'[Month],"yoy",[YOY Users),
[yoy]),
BLANK()
)

* YOY means from a given month in 2017, compared with the same month 2016, i.e. Jan-2016 vs Jan-2017.

[v-jiascu-msft]

Thanks for sharing with the Community.

Best Regards,

Dale

[v-jiascu-msft]

Hi @acanepa,

Which step can you get the max ID? Did you try it like this?

Measure 1 =
VAR MaxID =
    MAX ( [ID] )
VAR t1 =
    SUMMARIZE (
        'Calendar',
        [Month],
        "v1", CALCULATE ( [Dynamic Measure], [ID] = MaxID )
    )
RETURN
    AVERAGEX ( t1, [v1] )

If this couldn't help, could you please share a sample?

Best Regards!

Dale