topic Re: How to make a table in runtime, that contains several measures, and receives params from visuals in DAX Commands and Tips

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

szabofe — Wed, 03 Nov 2021 09:03:12 GMT

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.

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

amitchandak — Wed, 03 Nov 2021 09:55:59 GMT

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

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

szabofe — Wed, 03 Nov 2021 11:04:46 GMT

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?

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

Icey — Mon, 15 Nov 2021 08:06:17 GMT

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.

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

szabofe — Fri, 03 Dec 2021 12:09:43 GMT

Hi Icey ,

Thank you for your help.

It is I was looking for.
Great soulution.

Regards,
F.