Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GunnerJ
Post Patron
Post Patron

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

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

 

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

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 )
    )

 

View solution in original post

3 REPLIES 3
PC2790
Community Champion
Community Champion

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.

 

 

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

 

 

charge example.PNG

 

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
Community Champion
Community Champion

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 )
    )

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors