Forum Discussion

Brighton10's avatar
Brighton10
Helper II
5 years ago
Solved

Aggregate based on CardNumber and Action Date

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 invoic...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

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's avatar
Brighton10
Helper II
5 years ago

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