User Profile
stephane513
Regular Visitor
Joined 7 years ago
User Widgets
Contributions
Linking on aggregated columns or lookupvalue
Hello I have been developping a lot of pretty large datasets with tables typically containing 20M+ rows I often need to reconcile data between tables For example I have an item master with columns like owner / source / part number and others And I have a pricing table with owner / source / part and others Two methods are currently working for me 1- Create a calculated column in DAX to aggregate the three columns. Then make a relationship between the two tables and use related function 2- Use the lookupvalue in expression My opinion is that option 1 is better for performance. When having many lookupvalue the refresh time is taking longer and can fail due to memory constraints. I upgraded from 16Gb to 32 Gb to 64 Gb and I don't want to go on. On the service however it is rrefreshing ok in less than 40 minutes. Drawback with option 1 is the size of the pbix I did some tests. My original pbix is 1Gb. One of the table is containing 23 M rows and 40 columns. As soon as I create a key owner&source&part then the size goes to 1.5 Gb. When I create a second key on the other table it would go to 1.9 Gb. Loading to Service is not really the issue since I am using parameters or ALM Toolkit but still I don't want to end up with a pbix of 10 Gb Advantage of the option 2 is that the size of pbox remains quite reasonnable But when having a lot of lookupvalues the refresh would fail due to memory issues On the service once loaded the refresh time is quite similar from what I have seen which is a little surprising I would appreciate some guidance from any of you as I believe many people must have the same headache Thanks in advance StéphanePivoting Several Columns / List.Sum
Hello I have searching a lot in the messages but could not find a solution to my problem which I feel is pretty straight forward Here below a simplified extract from a table I want to work on I would want to transform that table having 7 columns Item / Sum Qty for FY=18 / Sum Sales for FY=18 / Sum Costs for FY=18 / Sum Qty for FY=19 / Sum Sales for FY=19 / Sum Costs for FY=19 I tried pivoting/unpivoting I tried with List.Sum(Table.SelectRows but wasn't able to achieve the result Thanks in advance for your support
