Forum Discussion
Ranking
As for dynamically updating, my interpretation was that the rank would change depending on the companies included in the filter/slicer. So for example, if only 2 companies are selected, the ranks would always be 1 & 2 since there are only 2 companies.
Thank you all for your help. I have one last question.
My dataset is pretty large, over 5 million rows. When i use Import, i am able to use these DAX commands and i have it working, but it is extremely slow. just filtering by a few companies may take a few minutes to reload. Is DirectQuery any quicker? I have read that you cannot use DAX in DirectQuery mode, since that is the case, how would i go about doing what i have done in import in directquery mode?
- You can use DAX with Direct Query but there are restrictions on functions you can use.
In general, any slicing/filtering on large data sets will take time and resources. If your data source supports Direct Query, it will generally be faster as the filtering will get pushed to the underlying DB engine rather than run inside the Power BI data model. I tried to use my existing RankX and CountX functions and they would not work in DirectQuery, are these supported and i was just doing something wrong, or are they not supported?
It looks like RankX is not supported:
https://msdn.microsoft.com/en-us/library/mt723603.aspx
However, if you go to the File > Options > Direct Query menu, you can check the box for unrestricted measures. This allows all DAX but won't necessarily work if the backend DB doesn't support it since with DQ the queries get sent to the DB for execution.
When i do my countx formula for the total it seems to work, but when i do my visual i get
Couldn't load the data for this visual
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' row.
Is this just saying that my dataset is too large for directquery?
Yes. You need to aggregate the data so that the rows returned are below that limit.
This may be a stupid question, but no question is stupid right...:smileyhappy:
How do I aggregate the data?
would i break the table into x number of tables, where x is the number of companies in the database? so each company has its own table and therefore its own information, then link them back up inside Power BI?
Direct Query is designed more to return aggregations that the source DB performs, rather than return the raw data. While filtering always consumes a lot of resources, with an import data model (rather than DQ) it is possible that your structure/model is partly to blame for the performance problems. You should be using a star schema with lookup tables and fact tables. I would recommend going back to an import data model and seeing if you can improve the model structure to get a better response time when slicing the data.
