This tutorial will discuss about RowNumber in DAX Studio. This can be a source of confusion because it shows up at some storage engine queries but it doesn’t exist in your data model. You’ll learn how these obstacles are generated, how they affect the whole calculation, and how to deal with them.
RowNumber shows up if you’re returning an entire table in your query. It can potentially slow down your DAX. So if you see this column, you have to filter down your query before you start iterating and executing, or start using the data cache.
The RowNumber Column
If you recall from the previous VertiPaq Analyzer modules, there was an example where you can see a RowNumber column in the VertiPaq Analyzer Metrics.
You can see that the cardinality is zero, which means there are no unique values. This happens when you bring your whole table in your query or when there is no unique key identifying your table.
One of the proper data modeling techniques is that you should have a unique or primary key in every table. Always make sure to build and format your queries properly to avoid performance hits. If you accidentally query your entire fact table, the storage engine will show a RowNumber like this in the xmSQL statement.
This will slow down your DAX because it will bring up all the other columns inside the fact table. You need to filter down the table in order to improve the performance of your DAX codes. There are a lot of ways to mitigate this problem using DAX or your data model.
If you see the RowNumber when you’re running your DAX or trying to produce the desired results, look back at your data model and DAX codes. Then, check if there are complications and apply appropriate solutions to maximize the performance of your measures.
If you have a good grasp of the whole ecosystem, you’ll know how to fix issues regarding slow performances, long execution durations, and inaccurate results.
***** Related Links *****
VertiPaq Storage Engine On Multi-Threaded Environment
VertiPaq Compression And Storage In Power BI
What Is DAX Studio In Power BI Desktop
Conclusion
The storage engine shows useful information that can help optimize your DAX codes. The RowNumber column is one of the information that you should pay attention to because it has the potential to slow down the DAX’s performance.
To learn more about the RowNumber column, try to practice what you’ve learned in DAX Studio. This will help you understand the storage engine more, and improve your report development skills. You may also check the previous blog posts with similar topics.
Nick