using field parameter inside a measure

[Maneko]

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:

SMA (# weeks) = AVERAGEX(

    DATESBETWEEN(Carro[Date],MAX(Carro[Date]) - (prmMA[prmMA_value]-1)*7, MAX(Carro[Date])),

    CALCULATE(SUM(Carro[DJM]))

    )

This report works perfectly for just one data column (DJM), but if I want display the same line chart for a different data column I must create another 5 measures for that new/different data column. The problem is that I have around 20 columns and I do not wish to create 100 measures...

I tried to use the field parameter the same way I have used the numeric parameter, trying to replace in the measures Carro[DJM] for prmField[prmField Opt], but if I click to display this line/measure, the chart shows a display error (SUM does not admit strings).

SMA (5 weeks) = AVERAGEX(

    DATESBETWEEN(Carro[Date],MAX(Carro[Date]) - (5-1)*7, MAX(Carro[Date])),

    CALCULATE(SUM(prmField[prmField Opt]))

    )

PS: I could no tfind how to upload here my pbix file.  Please use this link or check the pictures below: https://we.tl/t-mFnePxOlwp

Thanks a lot for any help you might provide!

[Maneko]

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!

[Rupak_bi]

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.

[OwenAuger · Accepted 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:

1. Use measures instead of columns within your prmField field parameter.
2. Create a calculation group with a single calculation item to handle the moving average.

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).