python
5 Topics
DAX standalone query execution using python
Hi, iwanted to execute DAX query through service principal in python standalone (vsCOde). but i got the error as below. Status Code: 401 {"error":{"code":"PowerBINotAuthorizedException","pbi.error":{"code":"PowerBINotAuthorizedException","parameters":{},"details":[],"exceptionCulprit":1}}}Solved1.3KViews0likes5Comments
Dax Studio - Python Connection
Hello All, There is a plan to move data source from one database to another for which it would be essential for us to understand the dashboards within a workspace. I have explored few options in Dax Studio which has capability to return Measures, Columns, Catalogs (Dashboards) and other details of a specific workspace. However if I need to get a detailed inforamtion across all the Power BI Dashboards in a workspace, the commands below would not allow me to. SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS, SELECT [ID],[Name] FROM $SYSTEM.TMSCHEMA_TABLES WHERE NOT [IsHidden] SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS WHERE NOT [IsHidden] SELECT [ID],[TableID],[Name],[QueryDefinition] FROM $SYSTEM.TMSCHEMA_PARTITIONS SELECT [ID],[TableID],[Name],[Expression] FROM $SYSTEM.TMSCHEMA_MEASURES WHERE NOT [IsHidden] SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS Requesting your guidance if there is a way I can loop through all the commands above and get the data into Excel format or into SQL or connect to Power BI directly so that I can analyze it further. Regards Mithun T1.1KViews0likes5Comments
Group by and count - How to translate this from python
Hi everyone, im new to powerbi DAX and im with a problem to solve... i have a table like this: COD_EVENT DEVICE_ID CITY DATE CAUSE 3002 134 CAMPINAS 01/02/2020 ELECTRICAL DISCHARGE 3005 147 CAMPINAS 02/10/2020 ANIMALS 4008 128 SAO PAULO 03/11/2020 WIND 4009 159 SAO PAULO 01/02/2021 ANIMALS 4013 128 SAO PAULO 02/05/2021 ELECTRICAL DISCHARGE 7892 178 CAMPINAS 01/11/2019 DEVICE FAIL ... ... ... ... i need to count from a date(m/y), for example dez/2020, the events in last 12 months, after this, i need groupby DEVICE, and show the quantity of events in devices that had more than one in last 12 months e the quantity of events in devices that had only one event in last 12 months. An example of what i expect to get: DATE CITY RECURRENT UNIQUE DEZ/2020 CAMPINAS 3 1 JAN/2020 CAMPINAS 3 2 FEV/2020 CAMPINAS 4 1 DEZ/2020 SAO PAULO 2 3 JAN/2020 SAO PAULO 2 4 FEV/2020 SAO PAULO 1 5 ... ... ... ... Where RECURRENT calculates the number of events in devices that had more than one event in the last 12 months since the date described in DATE, for the city described in CITY. And UNIQUE calculate de amount of events in devices that had only 1 event in last 12 months for that city. I make this work in python, and i did something like this: city = events_df['CITY'].unique() #Extract all cities present in dataframe tab_fin = pandas.DataFrame() #Create a df to append each iteration for i in range(0,len(city)): events_by_city = events_df[events_df['CITY'] == city[i]] #Create a table inside the loop that contains only the events of a especific city date_ini = pandas.to_datetime('2020-12-31') #first month of interest last_date = date.today().replace(day=1) - timedelta(days=1) #last month of interest num_months = (last_date.year - date_ini.year) * 12 + (last_date.month - date_ini.month) #number of dates between date_ini e last_date for j in range (0,num_months+1): events_12months = events_by_city[(events_by_city['DATE'] > date_ini + relativedelta(months=-12)) & (ocorr_conj['DT_INICIO'] <= data_ini)] #Extract the events in last 12 months events_grouped = events_12months.groupby('DEVICE_ID')['COD_EVENT'].count().reset_index() recurrent = sum(events_grouped[events_grouped['COD_EVENT']>1]['COD_EVENT']) #events in devices that had more than 1 unique = len(events_grouped[events_grouped['COD_EVENT']==1]) #events in devices that had only 1 tab_aux = pandas.DataFrame({'CITY':city[i], 'DATE':str(date_ini.month) +'/'+ str(date_ini.year), 'RECURRENT':recurrent, 'UNIQUE':unique}, index=[0]) tab_fin = tab_finappend(tab_aux) #Append in tab_fin the info of the i-city and j-month date_ini = date_ini + relativedelta(months=+1) #add 1 month to date676Views0likes1CommentUse Phyton script with table from DAX
Hi all I would like to know if it is possible to execute a Python script within Power BI in a table that had some columns added by DAX instructions. I know that it is possible with the Advanced Editor but the problem is that some columns created with DAX are not available in this editor so I cannot execute the Python scripts directly from the Advanced Editor. Thanks in advance1KViews0likes3Comments
Python to DAX Conversion
Hello, We are trying to convert the following python code into DAX. The goal is to distinct count of case number (caseno) grouped by (year), where occ date/time (occuron) is like YYYY Any help or assitance is greatly appreciated. Anything bolded is the column name in our dataset. Anything underlined denotes the table. The tables and columns would be indicated in DAX like: (e.g., 'table' and [column] would come from this: 'wa offense'[caseno] SELECT date_format(occuron, '%Y') AS occuronyear, count(DISTINCT caseno) AS dis_caseno FROM wa offense GROUP BY date_format(occuron, '%Y') ____________________ Specfic ibroff category: SELECT date_format(occuron, '%Y') AS occuronyear, count(DISTINCT caseno) AS dis_caseno, ibroff FROM wa offense GROUP BY date_format(occuron, '%Y'), ibroff Alternatively, is there a way to utilize python instead of DAX? Thank you!2.2KViews0likes1Comment