topic Re: Why my measure remove external date filter? in DAX Commands and Tips

Why my measure remove external date filter?

Sbezkoro — Sun, 05 Dec 2021 17:52:55 GMT

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!

Re: Why my measure remove external date filter?

AlexisOlson — Sun, 05 Dec 2021 18:23:50 GMT

Does this work better?

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

Re: Why my measure remove external date filter?

Sbezkoro — Sun, 05 Dec 2021 18:59:07 GMT

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 🙂

Re: Why my measure remove external date filter?

AlexisOlson — Sun, 05 Dec 2021 19:17:49 GMT

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.

Re: Why my measure remove external date filter?

Sbezkoro — Sun, 05 Dec 2021 19:25:52 GMT

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%

Re: Why my measure remove external date filter?

AlexisOlson — Sun, 05 Dec 2021 19:45:40 GMT

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.

Re: Why my measure remove external date filter?

Sbezkoro — Mon, 06 Dec 2021 10:39:52 GMT

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!