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. Tip: if you already have an attribution, add an extra one with the same key, filed and set AttributeHierarchyVisible to false, in order to use it for the MDX calculations only (I’ll explain the reasoning behind it in. =LASTDATE('Transaction Calendar'))Ī small change, that took it from 1m14s to 1m4s, saving 10 more seconds. You have to have an attribution you‘ll go through the count and any addictive measure with the required relationships. Next change I made was moving the separate filter statements into one by using ‘&’. 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. 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. 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. 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.
Often less expensive than building a dimension on the field.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. In other words creating a distinct count measure on a large cube where the field has a large number of distinct values is very expensive. If you build a distinct count measure on the field, then each field value will exist at every aggregation where it is involved. If you build a dimension on a field, each field value will physically exist once in the AS database. For more information on that option see my blog on Don't tell me that the fields have too many member so have dimensions built on them. Essentially you can ask AS to dynamically count the members at query time for any intersection. Another option is possible if you have dimensions on each (or both) of the two fields. For example, if the fields are integers and the second field is never larger than 99999, then you could multiply field1 by 100000 and add field2. How big are your two numbers? Perhaps you could "concatenate" the two fields into one. You can only have a distinct count on one numeric field.
0 kommentar(er)
