Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
User | Count |
---|---|
83 | |
76 | |
71 | |
68 | |
54 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |