topic Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel in Developer

VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Tue, 28 Apr 2026 09:22:45 GMT

VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

lbendlin — Tue, 28 Apr 2026 16:38:02 GMT

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

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

V-yubandi-msft — Mon, 04 May 2026 07:00:11 GMT

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.

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 08:32:32 GMT

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.

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 08:32:54 GMT

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 08:33:28 GMT

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 08:33:39 GMT

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 08:34:26 GMT

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

V-yubandi-msft — Mon, 04 May 2026 09:58:10 GMT

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.

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

RoySampad — Mon, 04 May 2026 10:55:45 GMT

Option Explicit Private Const PBI_WORKSPACE As String = "XXXXXX" Private Const PBI_DATASET As String = "XXXXXXXXX" Private Const INPUT_SHEET As String = "InputControlSheet" Private Const OUTPUT_SHEET As String = "OutputSheet" Private Const INPUT_CELL As String = "D3" Sub PullDataFromPowerBI_UsingYourDAX() Dim conn As Object Dim rs As Object Dim wsInput As Worksheet Dim wsOutput As Worksheet Dim daxQuery As String Dim testDate As Date Dim colNum As Long Set wsInput = ThisWorkbook.Worksheets(INPUT_SHEET) Set wsOutput = ThisWorkbook.Worksheets(OUTPUT_SHEET) ' Validate input date If Not IsDate(wsInput.Range(INPUT_CELL).Value) Then MsgBox "Please enter a valid date in " & INPUT_SHEET & "!" & INPUT_CELL, vbExclamation Exit Sub End If testDate = CDate(wsInput.Range(INPUT_CELL).Value) ' Build DAX query using the Excel cell date daxQuery = BuildDaxQuery(testDate) ' Optional: print query to Immediate Window for debugging Debug.Print daxQuery wsOutput.Cells.Clear Set conn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") On Error GoTo ErrorHandler conn.Open _ "Provider=MSOLAP;" & _ "Data Source=powerbi://api.powerbi.com/v1.0/myorg/" & PBI_WORKSPACE & ";" & _ "Initial Catalog=" & PBI_DATASET & ";" ' Forward-only / read-only rs.Open daxQuery, conn, 0, 1 ' Write headers For colNum = 0 To rs.Fields.Count - 1 wsOutput.Cells(1, colNum + 1).Value = rs.Fields(colNum).Name Next colNum ' Fast load to worksheet If Not rs.EOF Then wsOutput.Range("A2").CopyFromRecordset rs End If wsOutput.Cells.EntireColumn.AutoFit rs.Close conn.Close Set rs = Nothing Set conn = Nothing MsgBox "Success : Data loaded successfully into " & OUTPUT_SHEET & ".", vbInformation Exit Sub ErrorHandler: MsgBox "Error: " & Err.Description, vbCritical On Error Resume Next If Not rs Is Nothing Then If rs.State = 1 Then rs.Close End If If Not conn Is Nothing Then If conn.State = 1 Then conn.Close End If Set rs = Nothing Set conn = Nothing End Sub Private Function BuildDaxQuery(ByVal testDate As Date) As String Dim y As Long, m As Long, d As Long Dim q As String Dim dax As String y = Year(testDate) m = Month(testDate) d = Day(testDate) q = Chr(34) ' double quote character dax = "" dax = dax & "DEFINE" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __TestDate = DATE(" & y & ", " & m & ", " & d & ")" & vbCrLf dax = dax & "VAR __DateToUse =" & vbCrLf dax = dax & " FORMAT(__TestDate, " & q & "YYYY-MM-DD" & q & ")" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0FilterTable =" & vbCrLf dax = dax & " TREATAS({ __DateToUse }, 'DATE'[Date])" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0FilterTable2 =" & vbCrLf dax = dax & " TREATAS({" & q & "UNITED KINGDOM" & q & "}, 'XXXX'[Country])" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0FilterTable3 =" & vbCrLf dax = dax & " TREATAS({" & vbCrLf dax = dax & " " & q & "XXX - Condition 1" & q & "," & vbCrLf dax = dax & " " & q & "XXX - Condition 2" & q & "," & vbCrLf dax = dax & " " & q & "XXX - Condition 3" & q & "," & vbCrLf dax = dax & " }, 'XXXX'[&&&&&&&&&&&&])" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0FilterTable4 =" & vbCrLf dax = dax & " FILTER(" & vbCrLf dax = dax & " KEEPFILTERS(VALUES('XXXX'[Data Source]))," & vbCrLf dax = dax & " AND(" & vbCrLf dax = dax & " NOT('XXXX'[Data Source] IN {" & q & "Condition 1" & q & "})," & vbCrLf dax = dax & " NOT('XXXX'[Data Source] IN {" & q & "Condition 1" & q & "})" & vbCrLf dax = dax & " )" & vbCrLf dax = dax & " )" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0Core =" & vbCrLf dax = dax & " SUMMARIZECOLUMNS(" & vbCrLf dax = dax & " 'XXXX'[Reporting Date]," & vbCrLf dax = dax & " 'XXXX'[&&&&&&&&&&&&]," & vbCrLf <ALL OTHER COLUMNS> dax = dax & " 'DATE'[Date_Calculated]," & vbCrLf dax = dax & " __DS0FilterTable," & vbCrLf dax = dax & " __DS0FilterTable2," & vbCrLf dax = dax & " __DS0FilterTable3," & vbCrLf dax = dax & " __DS0FilterTable4," & vbCrLf dax = dax & " " & q & "Position" & q & ", CALCULATE(SUM('XXXX'[Position))," & vbCrLf <ALL OTHER COLUMNS> dax = dax & " " & q & "XXXXXXX" & q & ", CALCULATE(SUM('XXXX'[XXXXXX)]))" & vbCrLf dax = dax & " )" & vbCrLf dax = dax & vbCrLf dax = dax & "VAR __DS0BodyLimited =" & vbCrLf dax = dax & " TOPN(" & vbCrLf dax = dax & " 500000," & vbCrLf dax = dax & " __DS0Core," & vbCrLf dax = dax & " 'XXXX'[Reporting Date], 1," & vbCrLf dax = dax & " 'XXXX'[&&&&&&&&&&&&], 1," & vbCrLf <ALL OTHER COLUMNS> dax = dax & " )" & vbCrLf dax = dax & vbCrLf dax = dax & "EVALUATE" & vbCrLf dax = dax & " __DS0BodyLimited" & vbCrLf dax = dax & vbCrLf dax = dax & "ORDER BY" & vbCrLf dax = dax & " 'XXXX'[Reporting Date]," & vbCrLf dax = dax & " 'XXXX'[&&&&&&&&&&&&]," & vbCrLf <ALL OTHER COLUMNS> BuildDaxQuery = dax End Function

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

V-yubandi-msft — Tue, 05 May 2026 09:57:35 GMT

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 limit

Additionally, 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.

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

V-yubandi-msft — Fri, 08 May 2026 11:45:06 GMT

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

Re: VBA support (to pass cell value as a dax filter) needed to pull data from power bi to excel

V-yubandi-msft — Mon, 11 May 2026 12:04:39 GMT

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.