So it breaks down the DISTINCTCOUNT into distinct then count (well sum, but I hope you get what I mean). So in this case if the row is within the Valid From/To Date range, it returns a value of 1, then SUM’s all those 1’s up. Next for each of the rows in that returned table expression, it then evaluates the FILTER context. In this case it is a table expression DISTINCT(‘Transactions'). So it goes through each row in that table or table expression. Well the SUMX and the rest of the ‘X’ functions SUMX, MINX, MAXX, AVERAGEX, COUNTX and COUNTAX are iterators. This change to SUMX is the big change in dropping the time down to less than 6 seconds. So what to do about this bottle neck? Can we do DISTINCTCOUNT without doing DISTINCTCOUNT?Īfter hitting a search engine and consulting my worn copy of Power Pivot and Power BI by Rob Collie & Avichal Singh, yes there is another way of doing it. If I change the DISTINCTCOUNT to a SUM, the Power BI Report returns values in mere seconds, but the totals don’t make sense in the context of the transactions. OK with a few small changes we’ve found a saving of 20 seconds, however the biggest bottleneck is the distinct count. =LASTDATE('Transaction Calendar'))Ī small change, that took it from 1m14s to 1m4s, saving 10 more seconds. Next change I made was moving the separate filter statements into one by using ‘&’. The number of distinct basketID s in my dataset is nearly 32 million. There can then be multiple rows of the same basketID / customerID together with another ID for the item in the basket. each row contains a basketID and a customerID. This change dropped the time from 1m24s to 1m14s as it doesn’t have to covert formats between the two key columns in the background. 1 I have a cube where there is a single fact table about online customer orders. I set those to the same dd/MM/yyyy formats and reran the query. So after looking through the Data Model, I noticed that the formatting of the date on the data table was set as dd/MM/yyyy and on the Transaction table it was set as custom. Once that baseline was established, I could see the improvement (or not) of any changes that I made. So after the base line test, it took 1m24s to return the values. COUNTROWS ( DISTINCT ( table column ) ) DISTINCTCOUNT ( table column ) ) Copy Conventions 2. The following expressions are equivalent. However, DISTINCTCOUNT is better in that case. In this case a transaction has a start and end date when it is valid, and we want to see the total number of transactions between a specific date range, set by a slicer/visual on the Power BI report. The COUNTROWS function can be used to count the unique values available in a column for the current filter context. So here is the offending bit of code, basically doing a distinct count of transactions. Wow! When the query ran in that short of time, I went ‘No way… that’s not right, what have I done wrong?’, but no, after checking it was right. So I had a go at optimising it… and got it down to 6 seconds. It was taking 1 minute and 24 seconds to return the values after chewing through 2 million rows of data. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year.During the preparation for delivering a Power BI training session for a client, I was looking at the Tabular Data Model that was their data source, and I was struggling with a long running query. The three ideas for squeezing 2B+ distinct values into Tabular I have are split columns, split tables, and lowering precision, but of course each is but a workaround. The Date table must always start on January 1 and end on December 31, including all the days in this range. The first thing to consider is whether Tabular is actually a good fit for the scale of your data. All dates need to be present for the years required.The Date table must satisfy the following requirements: In order to use any time intelligence calculation, you need a well-formed date table. A table containing a single column of unique date values.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |