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

    ryan_mayu thank you. displays as expected

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      you are welcome