Formula in measure not working as expected

Question

Hi,&nbsp;&nbsp;I have attached images and labelled 4 measures below and the resulting output. Measure 1 and 2 are related, and measure 3 and 4 are related. Measure 1 and 3 are similar and measure 2 and 4 are similar, but I am trying to determine why measure 4 is returning a blank when measure 2 works correctly. Does it have to do with filter context? All of them work except for Measure 4.&nbsp;&nbsp;Measure 4 (Does Not Work):&nbsp;Measure 3 (Works Correctly):&nbsp;Measure 2 (Works Correctly):&nbsp;Measure 1 (Works Correctly):&nbsp;&nbsp;

alexisolson · Answer

Yes. It's likely due to filter context complications.&nbsp;If you expect [Fake Dispatched Available MW] to be the same value for each row you are iterating over in the FILTER, then the solution is simple; calculate it once rather than for each row:Price Setter (With Asset Removal) =
VAR Fake = [Fake Dispatched Available MW]
RETURN
    CALCULATE (
        MIN ( MeritOrder[Price] ),
        FILTER ( MeritOrder, Fake &lt;= MeritOrder[Cumulative Available MW] )
    )&nbsp;If the measure&nbsp;should be dependent on the row context from the FILTER iterator, then things get more complicated.

austinb97 · Answer

Thanks, however this does not appear to fix my issue with the BLANK getting returned. I just can't understand why Measure 2 works correctly and Measure 4 does not with essentially the same logic.&nbsp;

alexisolson · Answer

What if you replace&nbsp;[Fake Dispatched Available MW] with the hardcoded value 8953? Do you get a non-blank or are you just filtering out the whole table?&nbsp;Note: You are playing with fire having ALLSELECTED inside of a FILTER iterator. ALLSELECTED is really complicated to understand in such situations.

austinb97 · Answer

I appreciate the suggestion. You can see the result below, it does work as expected in this case. There is just some sort of issue with how the measure is being passed into this formula. When its hardcoded in, then it works. Any idea how to rewrite my "Measure 3" from above? You are probably correct that its the ALLSELECTED causing the issue. Basically I am using a slicer to choose an "AssetName" then I want it to sum the corresponding "Available MW" and pass it into my "Measure 4"&nbsp;

alexisolson · Answer

It's doesn't make sense to me why this works but my initial suggestion doesn't if 8953 is the result of a working Measure 3.&nbsp;Does anything change if you use VALUES instead of ALLSELECTED? If you don't have filter context from the visual (e.g. rows/column in a matrix), these should behave similarly.

Forum Discussion

Formula in measure not working as expected

6 Replies

Related content

Measure formula problem

If formula

formula

Expression.SyntaxError: Token ',' expected.

Measure Killer not working as expected. What I'm doing wrong?