So, counting customers/subscriptions has already been solved thanks to Alberto’s and your article here. I have a couple of million of these in my fact table. All customers has subscriptions with FromDate and ToDate on them, one row for each subscription, with an ID for the subscription which in addition to the customerid is the row identifier :ĬustomerID SubscriptionID FromDate ToDate This is a very common business problem, could be orders or subscriptions like I am working on now. The neat thing is that all three calculations can be used with any existing hierarchy or column in your Date-table and of course also on the Date-Level as the original calculation. So it’s up to you (and your model) which calculation you should prefer. The last calculation performs better on the small AdventureWorks-Model whereas on my customer’s model (16 Mio rows) the calculation in the middle performs best. I did a simple comparison in terms of query performance for AdventureWorks and also my customer’s model and results are slightly different: Calculation (Results in ms)Īs you can see, the original FILTER-calculation performs worst on both models. So you should test which calculation performs best in your scenario. I said it performs “different” as for all DAX calculations, performance also depends on your model, the data and the distribution and granularity of the data. CONTAINS ( VALUES ( ‘Date’ ),, ‘Date’ ).The first calculation you would usually come up with may look like this: Starts in the Time Period and ends after itįor my customer an order was considered as “open” if it was open within the selected Time Period, so in our case we need to count only Orders O1, O2, O3 and O4. Starts before the Time Period and ends in it Starts before the Time Period and ends after it Basically we have to differentiate between 6 types of Orders for our calculation and which of them we want to filter or not: The calculations shown in the white-paper only work for a single day so I had to come up with my own calculation to deal with this particular problem.īefore we can start we need to identify which orders we actually want to count if a Time Period is selected. the Open Orders in a given month – and not only for a single day. However, I recently had a slightly different requirement where I needed to calculate the Events-In-Progress for Time Periods – e.g. Alberto also wrote a white-paper summing up all their findings which is a must-read for every DAX and Tabular/PowerPivot developer. Now, my question is, how we can keep a check on our table columns, where we can know that, any of the our table's column is reaching the limit of 2B distinct values, so that we can take prior action on it to resolve.Īny suggestions or tips and tricks would be very helpful.Calculating the Events-In-Progress is a very common requirement and many of my fellow bloggers like Chris Webb, Alberto Ferrari and Jason Thomas already blogged about it and came up with some really nice solutions. Warning: With the restriction that no single column in the table can have more than 1,999,999,997 distinct values. The column which we got this error is a decimal data typeīut earlier also we got the same error on a string column, and we resolved it by splitting the column into two, and using those two columns in report by concatenating through measure. The ''column" of the ''table" cannot be processed because it contains more than two billion unique values. One of facttable, which has more than 10Billions of records, when we are trying to process that table, Our models are pulling data from SQL Server and the data is increasing rapidly in billions. We are using SSAS tabular models for our power bi reports.
0 kommentar(er)
