Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 structure
My calculateed column is called Status and is calculated in DAX as
Solved! Go to 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
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.
Hope this can help you.
Best Regards,
Cherry
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |