Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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 , 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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |