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
CAPEconsulting
Helper III
Helper III

First and Last Purchase

I am trying to create a calculated column that marks a "First" and "Last" purchase of a customer by product where reason is not 2 (reason 1 was initiation in the store and reason 3 was final transfer to another store) and score is not invalid (999). This is the sample table structuretable.JPG

My calculateed column is called Status and is calculated in DAX as

Status =
VAR
First = CALCULATE(MIN(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 1 && O[Score] <> 999))
VAR
Last = CALCULATE(MAX(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 3 && O[Score] <> 999))
RETURN
IF(O[Date] = First, "Pre", IF(O[Date] = Last, "Post", BLANK()))
 
It is working just fine for all the dataset except for the above scenario where the max date with a reason = 3 was not the overall max date for the customer. I can visually see that the row of date 5/12/17 should come up as the Post flag, but it still comes as blank. Why ??
 
Any suggestions ?
1 ACCEPTED SOLUTION

Actually, looks like I must have mistyped something somewhere, as my initial syntax works just fine - and will be perfect for large datasets too

Status =
VAR
First = CALCULATE(MIN(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 1 && O[Score] <> 999))
VAR
Last = CALCULATE(MAX(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 3 && O[Score] <> 999))
RETURN
IF(O[Date] = First, "Pre", IF(O[Date] = Last, "Post", BLANK()))

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @CAPEconsulting ,

For your requirement, please try the measure below.

Measure =
VAR first =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustID], 'Table'[Type] ),
            'Table'[Reason] = 1
                && 'Table'[Score] <> 999
        )
    )
VAR last =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustID], 'Table'[Type] ),
            'Table'[Reason] = 3
                && 'Table'[Score] <> 999
        )
    )
RETURN
    IF (
        MIN ( 'Table'[Date] ) = First,
        "Pre",
        IF ( MAX ( 'Table'[Date] ) = Last, "Post", BLANK () )
    )

Here is the output.

Capture.PNG

Hope this can help you.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey @v-piga-msft Thanks for that , So conceptually using FILTER before the ALLEXCEPT and having all FILTER statements in one is what you are proposing. While I think it will (and does) work, my undertstanding from @marcorusso  and @AlbertoFerrari  is that for large datasets filter should only be applied to relevant columsn through VALUES or SUMMARIZE functions and hence I had SUMMARIZE in there and seperated the ALLEXCEPT statemnet as it's own filter argument. Any suggestions of how this could be done for large data sets and still have great performance @OwenAuger  , @marcorusso  and @AlbertoFerrari  any suggestions ?

Actually, looks like I must have mistyped something somewhere, as my initial syntax works just fine - and will be perfect for large datasets too

Status =
VAR
First = CALCULATE(MIN(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 1 && O[Score] <> 999))
VAR
Last = CALCULATE(MAX(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 3 && O[Score] <> 999))
RETURN
IF(O[Date] = First, "Pre", IF(O[Date] = Last, "Post", BLANK()))

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.