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

Need Help

V-yubandi-msft
4 months ago
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.

V-yubandi-msft

Community Support

4 months ago

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.

RoySampad

Frequent Visitor

4 months ago

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

V-yubandi-msft
Community Support
4 months ago
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.

Forum Discussion

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

Related content

Measure/Model calculation support needed

Campaign Voucher FAQ & Support

Need Support

need help

Community account support