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 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).
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 |
---|---|
55 | |
25 | |
23 | |
14 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |