Forum Discussion

Usefulinfo's avatar
Usefulinfo
Helper I
1 year ago

Logical function OR issue

Hi All,

 

I need to show all active app by the end date of filter range date. Here, 'APP'[Received_Date] has active M-1 relationship with Date table and 'APP'[Closed_Date] has inactive relationship with date table


Created measure TotalActive to consider multiple conditions as below.

TotalActive =
VAR StartDate = CALCULATE(MIN('Date Table'[Date]), ALLSELECTED('Date Table'))
VAR EndDate = CALCULATE(MAX('Date Table'[Date]), ALLSELECTED('Date Table'))

RETURN
CALCULATE(
    COUNTROWS('APP'),
----Common condition
    'APP'[code] IN {"L1", "L2", "L3", "L4", "L5"} &&

    (
----1st OR condition to get all active app before the start date
        (
            'APP'[Received_Date] < StartDate &&
            ISBLANK('APP'[Closed_Date]) &&
            NOT CONTAINSSTRING('APP'[Stage], "WITHDRAW")
        )
|| ---2nd OR condition to get received app b/w the strat and end end
        (
            'APP'[Received_Date] >= StartDate &&
            'APP'[Received_Date] <= EndDate
        )
|| ---3rd OR condition to get the app received before start date and closed after end date ([Closed_Date] is not blank hence, this will not be retrieved by '1st OR condition' )
        (
            'APP'[Received_Date] < StartDate &&
            'APP'[Closed_Date] > EndDate &&
            NOT CONTAINSSTRING('APP'[Stage], "WITHDRAW")
        )
    )
)

 

But its only showing the data related to '2nd OR condition'. Please suggest how to get data for all OR conditions.

Need Help

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Usefulinfo , Try using below updated measure

    TotalActive =
    VAR StartDate = CALCULATE(MIN('Date Table'[Date]), ALLSELECTED('Date Table'))
    VAR EndDate = CALCULATE(MAX('Date Table'[Date]), ALLSELECTED('Date Table'))

    RETURN
    CALCULATE(
    COUNTROWS('APP'),
    'APP'[code] IN {"L1", "L2", "L3", "L4", "L5"} &&
    (
    (
    'APP'[Received_Date] < StartDate &&
    ISBLANK('APP'[Closed_Date]) &&
    NOT CONTAINSSTRING('APP'[Stage], "WITHDRAW")
    )
    ||
    (
    'APP'[Received_Date] >= StartDate &&
    'APP'[Received_Date] <= EndDate
    )
    ||
    (
    'APP'[Received_Date] < StartDate &&
    CALCULATE(
    'APP'[Closed_Date],
    USERELATIONSHIP('APP'[Closed_Date], 'Date Table'[Date])
    ) > EndDate &&
    NOT CONTAINSSTRING('APP'[Stage], "WITHDRAW")
    )
    )
    )

     

    • Usefulinfo's avatar
      Usefulinfo
      Helper I
      1 year ago

      bhanu_gautam , Thank you for your reply. Need to mension aggregate function for calculate. 

      I have included aggregate function for the suggested code of '3rd OR condition'  but It didn't work.  

       

      Error : A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      'APP'[Received_Date] < StartDate &&
      CALCULATE(
      max('APP'[Closed_Date]),
      USERELATIONSHIP('APP'[Closed_Date], 'Date Table'[Date])
      ) > EndDate &&
      NOT CONTAINSSTRING('APP'[Stage], "WITHDRAW")

       

      Could you please suggest.