Forum Discussion
VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel
VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel
Thank you for providing the complete VBA code. The discrepancy in row counts isn’t due to the DAX query itself DAX Studio returns all 11k rows but rather how Excel processes the ADODB recordset. By default, a forward-only cursor may stop early and not return all results.
A common fix is to adjust the recordset properties before opening, for example
rs.CursorLocation = 3 ' adUseClient rs.CursorType = 1 ' adOpenKeyset rs.LockType = 1 ' adLockReadOnly rs.MaxRecords = 0 ' no row limitAdditionally, ensure that .CopyFromRecordset isn’t restricted by MaxRows. You can refer to Microsoft’s documentation here: Range.CopyFromRecordset method (Excel) | Microsoft Learn
These adjustments are often suggested when importing larger datasets into Excel and should help you retrieve the full 11k rows from your Power BI dataset.
Please adjust these settings on your side and let us know how it works. If anything seems unclear or if I’ve misunderstood, just let us know.
12 Replies
- lbendlin
Super User
This should point you in the right direction
Update queries in Excel using VBA - Stack Overflow
Alternatively you can run the entire query from VBA.
excel - VBA Reptitive MDX query to Analysis Services - Stack Overflow
V-yubandi-msftCommunity Support
Hi RoySampad ,
Please refer to the response shared by lbendlin, along with the provided links they should help guide you toward resolving the issue.
A common workaround is to use VBA to update a parameter (or cell value), apply it within the query’s filter logic, and then refresh the query or connection.
If you need any additional details or clarification from our side, please let us know.
I am trying to execute my DAX Query from VBA code while passing in a date feild from a cell and it's running fine but the number of rows returned is getting truncated. When i run the same DAX query on DAX studio i get arounf 11k rows but the same query returns 6K rows on excel via VBA code. Any guidance on what's causing this would be appereciated.
- V-yubandi-msft
Thank you for the detailed explanation. Since the DAX query returns about 11,000 rows in DAX Studio but only about 6,000 rows via VBA in Excel, it seems the issue is with how the data is being retrieved in VBA, not with the query itself.
Issue is often related to the VBA or connection layer. Here are a few things to check
1. Ensure there are no limits set on the recordset, such as MaxRecords, cursor type, or fetch size if using ADODB
2. Verify that your VBA code is reading the entire recordset and not stopping before all rows are retrieved.
3. If you’re using .CopyFromRecordset, make sure it’s writing every row to the worksheet.
4. Check the connection or provider, as these can affect data retrieval.
Reviewing these points should help identify the cause.
I am trying to execute my DAX Query from VBA code while passing in a date feild from a cell and it's running fine but the number of rows returned is getting truncated. When i run the same DAX query on DAX studio i get arounf 11k rows but the same query returns 6K rows on excel via VBA code. Any guidance on what's causing this would be appereciated.
I am trying to execute my DAX Query from VBA code while passing in a date feild from a cell and it's running fine but the number of rows returned is getting truncated. When i run the same DAX query on DAX studio i get arounf 11k rows but the same query returns 6K rows on excel via VBA code. Any guidance on what's causing this would be appereciated.
I am trying to execute my DAX Query from VBA code while passing in a date feild from a cell and it's running fine but the number of rows returned is getting truncated. When i run the same DAX query on DAX studio i get arounf 11k rows but the same query returns 6K rows on excel via VBA code. Any guidance on what's causing this would be appereciated.
I am trying to execute my DAX Query from VBA code while passing in a date feild from a cell and it's running fine but the number of rows returned is getting truncated. When i run the same DAX query on DAX studio i get arounf 11k rows but the same query returns 6K rows on excel via VBA code. Any guidance on what's causing this would be appereciated...
- V-yubandi-msft
Hi RoySampad ,
We haven’t received any response as of now. Kindly take some time to review our response, and please let us know if any additional details or clarification are required from our side.
Thanks
- V-yubandi-msft
Hi RoySampad ,
We haven’t received any response as of now. Kindly take some time to review our response, and please let us know if any additional details or clarification are required from our side.
Thanks.
