Convert a Calculated Column into a Measure

[Anonymous]

Hi,

I have a line chart and a slicer visual. The slicer has four options to slice the line chart on. I have created the below calculated column to meet my needs but of course a calculated column isn't within the filter context so I've had to hard code my slicer value to obtain the correct result. How can I convert my calculated column into a measure to utilise the filter context? I tried to simply create a measure on my calculated column code but the measure doesn't like the "EARLIER" function.

Column = AVERAGEX(FILTER(ALL(Table1),(([YearWeekSort]>=EARLIER([YearWeekSort])-3) && ([YearWeekSort] <= EARLIER([YearWeekSort])-0) && [Status]="Closed" && [Project Type]="Slicer Value1")),[CountRefs])

Any help appreciated.

[MFelix]

Hi @Anonymous,

As you say the context is interfering in the measure. On measure the context is based on the filters, slicers and columns you use for your visual so the row context can be at different levels. try to change your measure to this:

Measure=
AVERAGEX (
    FILTER (
        ALL ( Table1 ),
        (
            (
                [YearWeekSort]
                    >= MAX ( [YearWeekSort] ) - 3
            )
                && (
                    [YearWeekSort]
                        <= MAX ( [YearWeekSort] ) - 0
                )
                && [Status] = "Closed"
                && [Project Type] = "Slicer Value1"
        )
    ),
    [CountRefs]
)

Regards,

MFelix

[Anonymous]

This doesn't work for me. The idea is to not hard code the "slicer value" within the measure, I want the dynamic slicer value to be used. When I remove your reference to the hardcoded slicer value I receive an average value of 34 when it should be 8.75.

[MFelix]

Hi @Anonymous,

You don't identify what is your slicer value, you only refer that you want to change a column to a measure, so the information is little.

Can you please give more context on this, some data and setup of the model so I can help you better.

Regards,

MFelix

[Anonymous]

Table1 structure has a count of IDs grouped by a year week and status column:

CountIDs | Year Week | Status |    Type

4                201838        Closed     Type1 

3                201838        Open       Type1

5                201837        Closed     Type1

7                201837        Open       Type1

4                201836        Closed     Type1

7                201836        Open       Type1

2                201835        Closed     Type1

3                201835        Open       Type1

The code I attached on my original post is the code for the "Closed" calculated column, I also have much the same for the "Open" calculated column. This code works, as in the "Closed" column would return the average of 4+5+4+2 = 3.75, but there is a "Type2", "Type3" and "Type4" also and I can't hardcode this into the column as this is a dynamic user choice, hence why I think I need my columns to be a measure in order to use the filter context from the slicer.

Ultimately I want to compare the "Closed" and "Open" values on a week by week basis within a line chart.

Thanks.

[MFelix · Accepted solution]

Hi @Anonymous,

Looking at your data and at your columns you need to make a diffente approach to your measure.

Measures are calculated based on context, what this means is that based on the columns used in the visual and the slicers in the page and the filters your measure make different calculation also be aware that adding specific filter to your measures also changes context.

Based on this and on your information you need to create the following measure:

Average past 3 weeks= 
CALCULATE(AVERAGE (Table1[CountIDs]);
    FILTER (
        ALL ( Table1[Year Week] );
        (
            (
                [Year Week]
                    >= MAX ( [Year Week] ) - 3
            )
                && (
                    [Year Week]
                        <= MAX ( Table1[Year Week] ) - 0
                )
        )
    ))

Then using your slicers on status and type it will change accordingly, and no need to put those in your filter since as I said the measures take into account the slicers values.

See below the image and PBIX file with the result, I also added Type 2 that is double of tyoe 1 so you can see that the use of more than one slicer can be use to change context.

Regards

MFelix