Forum Discussion

Sbezkoro's avatar
Sbezkoro
Regular Visitor
4 years ago
Solved

Why my measure remove external date filter?

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!
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Does this work better?

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

     

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Does this work better?

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

     

  • Sbezkoro's avatar
    Sbezkoro
    Regular Visitor
    4 years ago

    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's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Sbezkoro
        Regular Visitor
        4 years ago

        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%

  • Sbezkoro's avatar
    Sbezkoro
    Regular Visitor
    4 years ago

    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!