Logical function OR issue

[Usefulinfo]

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.

[Anonymous]

Hi @Usefulinfo  , 

Could you share the sample data and the expected outputs in visual?

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,

Wearsky

[bhanu_gautam]

@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]

@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.