Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi there,
It is possible to use a field parameter inside a measure?
I have a report where I display in a line chart different moving averages for one specific column (DJM).
Each line is drawn using a measure (5 in total), and the users can select which line(s) to show using a slicer.
I also allow the users to specify how many periods they want to use for a moving average. To do so I used a numeric parameter that I include in one of the measures (SMA #) like this:
Solved! Go to Solution.
Hi @Maneko
Field parameters cannot be used within a DAX expression to create a "dynamic" reference to the underlying field.
However there are different methods to get the result you want.
I would suggest:
The field parameter then allows selection of which measure(s) to visualize (i.e. which column to sum) and the calculation group applies the moving average calculation to the selected measure(s).
Attached is my attempt using your PBIX.
1. Created measures to SUM each column:
DJM Sum =
SUM ( Carro[DJM] )
GOL Sum =
SUM ( Carro[GOL] )
// etc.
2. Updated the field parameter to reference these measures:
-------------------------------
-- Calculated Table: 'prmField'
-------------------------------
TABLE prmField =
{
("DJM", NAMEOF([DJM Sum]), 0),
("GOL", NAMEOF([GOL Sum]), 1),
("IBI", NAMEOF([IBI Sum]), 2),
("LHB", NAMEOF([LHB Sum]), 3),
("SAO", NAMEOF([SAO Sum]), 4),
("SCA", NAMEOF([SCA Sum]), 5)
}
3. Created this calculation group:
--------------------------------------
-- Calculation Group: 'Moving Average'
--------------------------------------
CALCULATIONGROUP 'Moving Average'[Moving Average Option]
CALCULATIONITEM "SMA (# weeks)" =
VAR MaxDate = MAX ( Carro[Date] )
RETURN
AVERAGEX (
DATESBETWEEN (
Carro[Date],
MaxDate - ( [prmMA_value] - 1 ) * 7,
MaxDate
),
SELECTEDMEASURE ( )
)
4. Then assemble the chart with the field parameter on Y-axis and the calculation item applied as a visual-level filter:
Hopefully this helps even if it needs to be tweaked.
Regards
Owen 🙂
P.S. Only super users can directly attach files to posts, so you have to use cloud storage or similar (as you have already).
Thanks a lot for providing the pbix file. It does indeed works as needed.
I will look into detail about calculation groups as it seems I can use them in other scenarios.
Have a good day Sir!
to use the same measure for all the colums, you need to unpivot all the colums and then the category column to can be used as slicer for different column.
Hi @Maneko
Field parameters cannot be used within a DAX expression to create a "dynamic" reference to the underlying field.
However there are different methods to get the result you want.
I would suggest:
The field parameter then allows selection of which measure(s) to visualize (i.e. which column to sum) and the calculation group applies the moving average calculation to the selected measure(s).
Attached is my attempt using your PBIX.
1. Created measures to SUM each column:
DJM Sum =
SUM ( Carro[DJM] )
GOL Sum =
SUM ( Carro[GOL] )
// etc.
2. Updated the field parameter to reference these measures:
-------------------------------
-- Calculated Table: 'prmField'
-------------------------------
TABLE prmField =
{
("DJM", NAMEOF([DJM Sum]), 0),
("GOL", NAMEOF([GOL Sum]), 1),
("IBI", NAMEOF([IBI Sum]), 2),
("LHB", NAMEOF([LHB Sum]), 3),
("SAO", NAMEOF([SAO Sum]), 4),
("SCA", NAMEOF([SCA Sum]), 5)
}
3. Created this calculation group:
--------------------------------------
-- Calculation Group: 'Moving Average'
--------------------------------------
CALCULATIONGROUP 'Moving Average'[Moving Average Option]
CALCULATIONITEM "SMA (# weeks)" =
VAR MaxDate = MAX ( Carro[Date] )
RETURN
AVERAGEX (
DATESBETWEEN (
Carro[Date],
MaxDate - ( [prmMA_value] - 1 ) * 7,
MaxDate
),
SELECTEDMEASURE ( )
)
4. Then assemble the chart with the field parameter on Y-axis and the calculation item applied as a visual-level filter:
Hopefully this helps even if it needs to be tweaked.
Regards
Owen 🙂
P.S. Only super users can directly attach files to posts, so you have to use cloud storage or similar (as you have already).
User | Count |
---|---|
53 | |
52 | |
20 | |
17 | |
16 |
User | Count |
---|---|
112 | |
52 | |
44 | |
28 | |
22 |