Forum Discussion

RoySampad's avatar
RoySampad
Frequent Visitor
4 months ago
Solved

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
Need Help
  • V-yubandi-msft's avatar
    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's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
4 months ago

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.

  • RoySampad's avatar
    RoySampad
    Frequent Visitor
    4 months ago

    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's avatar
      V-yubandi-msft
      Icon for Community Support rankCommunity 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's avatar
        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
  • RoySampad's avatar
    RoySampad
    Frequent Visitor
    4 months ago

    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.