1

I am running a DAX query within a Python script (using adodbapi) and would like to extract data from a table (called data) inbetween two dates (the field 'date_created'), however I am getting a syntax error.

tablestring = 'EVALUATE(CALCULATETABLE(Data,DATESBETWEEN(Data[date_created], 01/02/2021,04/02/2021)))'

df = pd.read_sql(tablestring, conn)
Sanch
  • 367
  • 2
  • 11

1 Answers1

1

Here's a pattern that might work;

  • unpack the adoadapi results with a function to get it into a pandas frame
  • If you have some upstream process that handles this part using a conn object that read_sql can use, then you might only need to use the date() function on your DATESBETWEEN inputs; hard to see without the rest of your implementation code
  • sample code:

`

    import adodbapi
    import pandas as pd
    import numpy as np
    
    workspace = 'your published workspace'
    report = 'report_name'
    
    sec = 'Integrated Security=SSPI'
    conn = adodbapi.connect(f"Provider=MSOLAP.8;Data Source='powerbi://api.powerbi.com/v1.0/myorg/{workspace}';Initial Catalog={report};{sec};")
    cur = conn.cursor()
     
    def get_df(data):
        ar = np.array(data.ado_results) # turn ado results into a numpy array
        df = pd.DataFrame(ar).transpose() # create a dataframe from the array
        df.columns = data.columnNames.keys() # set column names
        return df
    
    your_table_name = "Data"
    your_column_name = "date_created"
    
    DAX_Query = f'''
    DEFINE
      VAR FilteredTable = 
        FILTER(
           '{your_table_name}',
            AND(
                '{your_table_name}'[{your_column_name}] >= DATE(2021, 1, 2),
                '{your_table_name}'[{your_column_name}] < DATE(2022, 4, 2)
              )
        )
    
    EVALUATE
      FilteredTable
    '''
     
    cur.execute(DAX_Query)
    data=cur.fetchall()
    demo_df = get_df(data)

`

Steve_datasci
  • 66
  • 4