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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.