Flag applying to all rows of account instead of line by line

[GunnerJ]

I have a flag measure that's supposed to tell when a charge is active/inactive depending on a sliding date filter. Currently it appears to "flag" as active if ANY charge is active during the filtered date but I want it to only show on a row by row basis. The code is below.

ACTIVE BY CHARGE DATES = 

VAR stop_dt = CALCULATE(max(CHARGE_DATES[Date]), USERELATIONSHIP('MASTER-CHARGES'[BI_STOP_DT],CHARGE_DATES[Date]))

VAR start_dt = CALCULATE(max(CHARGE_DATES[Date]), USERELATIONSHIP('MASTER-CHARGES'[BI_START_DT],CHARGE_DATES[Date]))

RETURN CALCULATE(DISTINCTCOUNT('MASTER-CHARGES'[BI_ACCT]),'MASTER-CHARGES'[BI_START_DT] <= start_dt ,'MASTER-CHARGES'[BI_STOP_DT] > stop_dt , NOT(ISBLANK('MASTER-CHARGES'[BI_START_DT])))

in the picture below I've focused in on 1 account and the filtered date is 5/9/2022. I would expect only the last rows with start dates of April 12th and year 3000 stop dates to have a flag but all of the rows of this account are flagged. I'm really unsure why that's the case. 

https://www.dropbox.com/s/ou3we0dok0vpqfb/Testcharges.pbix?dl=0

[PC2790]

The formula logic looks incorrect as you are taking max dates for stop date and start date both.

In both the cases, the stop date and start date will be returned as 5/10/2022 and that's why flag is always 1.

[GunnerJ]

@PC2790 thank you for the reply.

The dates showing 5/10/2022 is the desired outcome. It can change depending on the slider's value. In that example if a charge code's start date is <= 5/10/2022 and the stop date is > 5/10/2022 then I know they're active. If I move the slider to 4/9/2021 I can tell if a charge was active at that time. The picture didn't load in my last post but I reuploaded it here. Given the logic we discussed I'm just confused as to why charges with start times of August 2021 and stop times of Dec 2021 would still show as active.

I created a sepereate measure just to see if it'd work but it gives the same result. 

TEST = 

VAR selected_date = CALCULATE(Max(CHARGE_DATES[Date]),ALLSELECTED(CHARGE_DATES))

RETURN CALCULATE(DISTINCTCOUNT('MASTER-CHARGES'[BI_ACCT]),'MASTER-CHARGES'[BI_START_DT] <= selected_date ,'MASTER-CHARGES'[BI_STOP_DT] > selected_date, NOT(ISBLANK('MASTER-CHARGES'[BI_START_DT])))

[PC2790 · Accepted solution]

What is you create a new column with the below code and then use this column in your visualisation

ACTIVE BY CHARGE DATES Col =
VAR stop_dt =
    CALCULATE (
        MAX ( CHARGE_DATES[Date] ),
        USERELATIONSHIP ( 'MASTER-CHARGES'[BI_STOP_DT], CHARGE_DATES[Date] )
    )
VAR start_dt =
    CALCULATE (
        MAX ( CHARGE_DATES[Date] ),
        USERELATIONSHIP ( 'MASTER-CHARGES'[BI_START_DT], CHARGE_DATES[Date] )
    )
RETURN
    IF (
        'MASTER-CHARGES'[BI_START_DT] <= start_dt
            && 'MASTER-CHARGES'[BI_STOP_DT] > stop_dt
            && NOT ( ISBLANK ( 'MASTER-CHARGES'[BI_START_DT] ) ),
        1,
        0
    )

As I am not fully sure, what you are trying to do here, so I have not changed the logical conditions. Just converted them to an If condition to show 0 or 1.

See if this is giving you the desired result.

And if you are looking for the number of active accounts within the selected time frame, try using the below code:

Active =
VAR FirstDay =
    CALCULATE ( MIN ( 'CHARGE_DATES'[Date] ), ALLSELECTED ( 'CHARGE_DATES'[Date] ) )
VAR LastDay =
    CALCULATE ( MAX ( 'CHARGE_DATES'[Date] ), ALLSELECTED ( 'CHARGE_DATES'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( 'MASTER-CHARGES'[BI_ACCT] ),
        DATESBETWEEN ( CHARGE_DATES[Date], FirstDay, LastDay )
    )