CODEWITHSUNDEEP
exceldaxpowerbi-desktop
Dan Paul
Dan Paul

Reputation: 1

Formula to return value from a range will not work in a DAX measure, but works in a DAX Column

This is driving me nuts. I have never encountered this before.

I have a table (Excel Based called 'Metric Ranges' That contains a series of Metrics and their scoring range values (Lower Limit and Upper Limit).

Metric Values Table

I need to create a measure that will pass in the the Metric's ID, and Value into this table and return the score based on the ranges. The formula below works fine in a table column and returns the Score, but for some reason as a Measure, it will not return the value. When I test and return all the VAR's I get values returned, so I know it's not the values there, it is the actual Calculation and that is not working. The Score returned is a blank value.

Net Savings Year to date =
VAR MetricValue = [Savings % Annualize Goal]
VAR MetricPrgm = "4003"
VAR MetricId = 1
VAR Score =
    CALCULATE(
        VALUES( 'Metric Ranges'[Score] ),
        FILTER(
            'Metric Ranges',
            'Metric Ranges'[MetricId] = MetricId
                && 'Metric Ranges'[Program] = MetricPrgm
                && MetricValue > 'Metric Ranges'[Lower Range]
                && MetricValue <= 'Metric Ranges'[Upper Range]
        )
    )
RETURN
    MetricId

Upvotes: 0

Views: 309

Answers (1)

sergiom
sergiom

Reputation: 4887

When a calculated column is evaluated, a row context exist and to have the corresponding filter context a CALCULATE is required, to trigger a context transition.

When a measure is evaluated instead, there is the filter context but no row context.

This means that when evaluating the FILTER( 'Metric Ranges' ...) in the measure, only the Metric Ranges portion in the filter context is used. Instead, in the calculated column the whole table is used. To make this code work we must change it to work with a filter context. This might be a possible solution

Net Savings Year to date =
VAR MetricValue = [Savings % Annualize Goal]
VAR MetricPrgm = "4003"
VAR MetricId = 1
VAR Score =
    CALCULATE(
        SUM( 'Metric Ranges'[Score] ),
        FILTER(
            ALL( 'Metric Ranges' ),
            'Metric Ranges'[MetricId] = MetricId
                && 'Metric Ranges'[Program] = MetricPrgm
                && MetricValue > 'Metric Ranges'[Lower Range]
                && MetricValue <= 'Metric Ranges'[Upper Range]
        )
    )
RETURN
    MetricId

Upvotes: 0

Related Questions