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 community,
My Data model has two table :
1. f_visit_info Table to store visit and customer's action.
2. f_customer_info
I want to create a custom column show the last f_visit_info[visit_date] of each f_customer_info[customer_id].
Option 1 : Create intermediate talbe which turned off "Enable load"
--> goup by [customer_id] , agregate visit_date with List.LAst()
--> Merge f_customer_info with this intermediate table
Option 2 : Add custom column in f_cutomer_info
= Table.Group(f_visit_info,{"customer_id"},{"last_visit", each List.Last([visit_date])}) [last_visit]{0}
Issue is f_visit_info has somany query after loading from data source. I wonder if it will be repeat again in the process in Option 2, which will add another significant time to refreshing for repeating f_visit_info ETL query.
PS : I don't know why but when I added DAX custom column, it will make every "edit / add measure" action take very long time --> So I prefers to finish it before loading into the model with M power query;
Solved! Go to Solution.
This kind of thing should be done on the DAX side. If you provide example data, a specific solution can be provided. However, it is possible in M also. You can merge the visit table into the customer table on the customer_id column. Don't expand the merged table, but add a custom column with = List.Max([MergedColumn][visit_date]). That should give the max date from the visit_date column for that customer. You can then remove the MergedColumn.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This kind of thing should be done on the DAX side. If you provide example data, a specific solution can be provided. However, it is possible in M also. You can merge the visit table into the customer table on the customer_id column. Don't expand the merged table, but add a custom column with = List.Max([MergedColumn][visit_date]). That should give the max date from the visit_date column for that customer. You can then remove the MergedColumn.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@tanat_inc , You can create a measure
max( last f_visit_info[visit_date] ) and use that in visual with f_customer_info[customer_id] or name
or a new column in f_customer_info
maxx(filter( f_visit_info , f_visit_info[customer_id] = f_customer_info[customer_id] ),f_visit_info[visit_date])
Thanks for your answer. Could you instructed me how to do this in power query instead ?
- I don't know why, but when I add DAX custom column to my model it wull cause significantly longer loading time when I create any new measure after that.
>> That's why I try to avoid DAX ustom column, and want to do it in M Power query.
- Objective is to list the name of customer, the last time he visit, and how long they missing in table (matrix visual) along with their contact info.
>> As I also need to display it on matrix visual, I need to create new column.
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |