Aggregate based on CardNumber and Action Date

[Brighton10]

Hi Power BI community

I have 2 tables [event] and [target]. Event table contains cardnumber, country, action date and type. Output contains cardNumber, country, target_date, Until_date and invoice _date. I want to create a table that contains the cardNumber, country, target_date, Until_date and an Action based on a condition. For example for cardNumber = CN100 in the period (target_date: 2021/01/02) to (Until_date: 2021/01/06) if the type contain 'OK' I want it to show 'Viewed Message' else if Type contains 'OK' and 'Delete' i want to show 'Viewed and Deleted' then finally if type is only 'delete' then show 'deleted'. i have attached some images to illustrate what the final outcome needs to look like. Thank you for help

[ryan_mayu · Accepted solution]

@Brighton10 

maybe you can create a column in target table

Column = 
VAR _event=maxx(FILTER(Event,'Event'[Action Date]>=Target[target_date]&&Event[Action Date]<=Target[until_date]&&Event[CardNumber]=Target[CardNumber]&&Event[Country]=Target[Country]),'Event'[Action Date])
VAR _ok=maxx(FILTER(Event,'Event'[Action Date]>=Target[target_date]&&Event[Action Date]<=Target[until_date]&&Event[CardNumber]=Target[CardNumber]&&Event[Country]=Target[Country]&&Event[Type]="ok"),Event[Type])
VAR _del=maxx(FILTER(Event,'Event'[Action Date]>=Target[target_date]&&Event[Action Date]<=Target[until_date]&&Event[CardNumber]=Target[CardNumber]&&Event[Country]=Target[Country]&&Event[Type]="delete"),Event[Type])
return if(NOT(ISBLANK(_ok))&&NOT(ISBLANK(_del)),"viewed and deleted",if(NOT(ISBLANK(_del)),"deleted",if(not(ISBLANK(_ok)),"viewd message",if(NOT(ISBLANK(_event)),"not viewd",BLANK()))))

pls see the attachment below

[Brighton10]

I have been playing around with the query and this also produces the same result:

Action =

VAR smartcard_num = event[CardNumber]

VAR calc =

CALCULATE(

IF(CONTAINS(event,event[Type],"ok") && CONTAINS(event, event[Type], "delete"),"viewed and deleted message",

IF (

CONTAINS ( event, event[Type], "ok" ),

"viewed message",

IF ( CONTAINS ( event, event[Type], "delete" ), "deleted message"

)

)),FILTER(ALL(event),event[CardNumber]=EARLIER(event[CardNumber]) && event[action_Month]=EARLIER(event[action_Month])))

RETURN

calc

[Brighton10]

@ryan_mayu thank you. displays as expected

[ryan_mayu]

you are welcome