Why my measure remove external date filter?

[Sbezkoro]

I wish to calculate free capacity which is math difference between 100% and Loading, %.

[Loading, %] - is a measure which calculated from inputs file as:

Loading, % =

    CALCULATE( SUM(InputFiled[Value]),

        Consolidated[RESOURCE TYPE ] IN {"Loading"})

My formula for Free Capacity is:

Free Capacity, % =

var Diff = 1-[Loading, %]

Return

IF(Diff>0,Diff,0)

When i create matrix table for Free Capacity, % by month, its shows correct values but it is not filtered by date slicer and shows  table for all months available in database.

If i replace table value by [Loading, %] all works fine.

How I shold modify Free Capacity formnula to have it responcive to date slicer?

Thanks!

[Sbezkoro]

In order to make it work I replaced all nulls with "0" in query (inputs file). It, unfortunatelly increased my dataset, but at least I have now only zeros (no empty) and it works fine.

Thanks!

[Sbezkoro]

I modefied IF function as per my needs and its works, solution is excepted, thanks a lot!!

Now need to understand what was wrong with mine 🙂

[AlexisOlson]

Your original measure always returned a value regardless of date since if [Loading, %] were blank, it would still return 1 - BLANK() = 1.

My measure specified that it should only return something if [Loading, %] is not blank.

[Sbezkoro]

and if I need to return 100% if Loading, % is zero or blank in inputs? i'm asking because equation is

Loading, % + Free Capacity, % = 100%

[AlexisOlson]

What I suggested should work fine for 0%.

If it's blank, then things are more difficult because there's no way to tell from a blank why it's blank (and you apparently want to treat certain cases of blank differently).

If there are some blank values that you want to interpret as zero, then you'll need to build in logic to specify why those blanks are different from other blanks. This is possible but I don't have sufficient context to write the appropriate logic for you.

[AlexisOlson · Accepted solution]

Does this work better?

Free Capacity, % =
VAR Loading = [Loading, %]
RETURN
    IF (
        ISBLANK ( Loading ),
        BLANK (),
        MAX ( 1 - Loading, 0 )
    )