How to make a table in runtime, that contains several measures, and receives params from visuals

[szabofe]

Hi Community,

I would like to construct a table in a given format. It is given by the national energy office.
My aproach to resolve the problem:

EnergyStatTable = 
VAR SelYear = SELECTEDVALUE('DateTable'[Year], 2021)
VAR B1 = 0
VAR B2 = 
    CALCULATE(
        '_QuantityMeasures'[ElectricityConsumption (kWh)], 
        FILTER('DateTable', [Year] = SelYear)
    ) 
VAR B3 = 0
RETURN 
    SELECTCOLUMNS(
        {
            (1, "self-generated electricity", B1), 
            (2, "bought electricity", B2), 
            (3, "derived heat", B3)
        }, 
        "C. Code", [Value1], 
        "A. Name of energy carrier", [Value2], 
        "B. Amount of energy used", [Value3]
    )

The problem:

It is working when I am using constant "VAR SelYear = 2020".
I suppose the table is evaluated before the visual loads in.

Could someone give me directions?
Whitch is the best practice situations like this?

Regards,
F.

[Icey · Accepted solution]

Hi @szabofe ,

If you just want to show the result into a table visual, here is a workaround.

To my understanding, only "B2" is dynamic and '_QuantityMeasures'[ElectricityConsumption (kWh)] is a measure. Right?

If so, just use "enter data" to create a table like so:

Then, create a measure like so:

B. Amount of energy used =
VAR SelYear =
    SELECTEDVALUE ( 'DateTable'[Year], 2021 )
VAR B1 = 0
VAR B2 =
    CALCULATE (
        '_QuantityMeasures'[ElectricityConsumption (kWh)],
        'DateTable'[Year] = SelYear )
    )
VAR B3 = 0
RETURN
    SWITCH ( MAX ( 'Table'[C. Code] ), 1, B1, 2, B2, 3, B3 )

And put "C. Code", "A. Name of energy carrier" and "B. Amount of energy used". The screenshot below is just a sample.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[szabofe]

Hi @Icey ,

Thank you for your help.

It is I was looking for.
Great soulution.

Regards, 
F.

[amitchandak]

@szabofe , You can not use selected values/slicer values in a calculated table.

You can only use that as var in measure

and then you can use sumx, minx etc to get final value

[szabofe]

Thank you @amitchandak for the quick answer.
As I understand I can not make this table dynamic depending on a slicer.
The table collects heterogen measures. How can I make the user able to chose a year?