Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
I have been playing around with the query and this also produces the same result:
you are welcome
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
105 | |
88 | |
75 | |
67 |
User | Count |
---|---|
123 | |
112 | |
96 | |
82 | |
72 |