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

[Anonymous]

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 Ranges Table

I need to create a measure that will pass in the the MetricId, and MetricValue into this table and return the score based on the ranges for the metric. 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.  Any ideas why this will not work?  

Forumla is:

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]

)
)

[parry2k · Accepted solution]

@Anonymous I assume the "metric range" table is not having a relationship with any table? Correct?

Try changing Metric Range to ALL ( Metric Range) in the filter.

[parry2k]

@Anonymous good to know. Enjoy!!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous that seems to be the problem, as I mentioned use ALL and test it and then we can take it from there.

[parry2k · Accepted solution]

@Anonymous I assume the "metric range" table is not having a relationship with any table? Correct?

Try changing Metric Range to ALL ( Metric Range) in the filter.

[Anonymous]

That's the ticket!   It worked.  

[Anonymous]

It has a relationship with the Calendar Table - Effective Data : Date

[parry2k]

@Anonymous can you share pbix file with the sample data? I'm assuming you checked the following var returning the value that falls between the range.

VAR MetricValue = [Savings % Annualize Goal]

[Anonymous]

Because of company privacy I cannot share the .pbix file.   But, I can confirm that the measure returns a value.  In fact all the VAR's return values.  The Calculate is just not working in the measure, but it works when used in a Table Column.  

[Savings % Annualize Goal] does have a value.  If I switch the the 'Return' to [Savings % Annualize Goal] the value is returned.