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...
Need Help
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.