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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

Help with code for looking at previous service order type

I have some code the works most of the time, but I'm a bit confused when it seems to lose it's logic. 

 

The custom column "Last SO" is supposed to show the BI_SO_TYPE_CD of the service orders that came before but only when they switch. In the picture below you can see the "ADD" service orders were first and all of the "CUTOFF" service orders follow and have "ADD" as the Last SO. That is how it's supposed to work. However at the very bottom the Last SO shows "DISC" when it should be "CUTOFF". I really can't explain why this is and could use some help. 

 

GunnerJ_0-1638214757173.png

 

Here is the dax for the custom column. Its trying to look to make sure the account is the same, as well as the BI_SRV_MAP_LOC. It then returns the last SO_TYPE that wasn't the current SO_TYPE. 

Last SO =
CALCULATE(lASTNONBLANK('SO Attrition'[BI_SO_TYPE_CD],1),FILTER('SO Attrition', 'SO Attrition'[BI_ACCT] = EARLIER('SO Attrition'[BI_ACCT]) && 'SO Attrition'[NEEDED_DT] < EARLIER('SO Attrition'[NEEDED_DT]) && 'SO Attrition'[SRV_MAP_LOC] = EARLIER('SO Attrition'[SRV_MAP_LOC])&& 'SO Attrition'[BI_SO_TYPE_CD] <> EARLIER('SO Attrition'[BI_SO_TYPE_CD])))
 
Link. Screenshot is from "page 4"

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @GunnerJ 

 

There will be some logical loops in your formula, resulting in wrong results. I calculate the last date first, and then output the corresponding BI_SO_TYPE_CD.

Add two calculated columns:

Last SO date = 
CALCULATE (
   MAX('SO Attrition'[NEEDED_DT]),
    FILTER (
        'SO Attrition',
        'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[NEEDED_DT] < EARLIER ( 'SO Attrition'[NEEDED_DT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
             &&NOT(ISBLANK('SO Attrition'[BI_SO_TYPE_CD])
    )
))
Last SO New = 
CALCULATE (
    MAX ( 'SO Attrition'[BI_SO_TYPE_CD] ),
    FILTER (
        'SO Attrition' ,
        [NEEDED_DT] = EARLIER ( 'SO Attrition'[Last SO date] )
            && 'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
    )
)

 The new result is shown in the figure.

vzhangti_0-1638432187286.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

Hi, @GunnerJ 

 

There will be some logical loops in your formula, resulting in wrong results. I calculate the last date first, and then output the corresponding BI_SO_TYPE_CD.

Add two calculated columns:

Last SO date = 
CALCULATE (
   MAX('SO Attrition'[NEEDED_DT]),
    FILTER (
        'SO Attrition',
        'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[NEEDED_DT] < EARLIER ( 'SO Attrition'[NEEDED_DT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
             &&NOT(ISBLANK('SO Attrition'[BI_SO_TYPE_CD])
    )
))
Last SO New = 
CALCULATE (
    MAX ( 'SO Attrition'[BI_SO_TYPE_CD] ),
    FILTER (
        'SO Attrition' ,
        [NEEDED_DT] = EARLIER ( 'SO Attrition'[Last SO date] )
            && 'SO Attrition'[BI_ACCT] = EARLIER ( 'SO Attrition'[BI_ACCT] )
            && 'SO Attrition'[SRV_MAP_LOC] = EARLIER ( 'SO Attrition'[SRV_MAP_LOC] )
    )
)

 The new result is shown in the figure.

vzhangti_0-1638432187286.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.