FAQ Database Discussion Community


Calculate the number of business days between two dates in power pivot

powerpivot,dax,powerbi,powerquery
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot. I do the same in T-SQL using the following query DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END...

PowerBI hierarchy creation without the cloud

excel,hierarchy,powerpivot,powerview,powerbi
Right now we can create hierarchies in PowerPivot but I'm pretty sure I can't do it in PowerBI. I could create a Data Model in PowerPivot and use that for PowerBI I think. Is there any other work around that could be more desktop based? Thanks! Mike...

Power BI REST API: Import data

powerbi
Is it possible to import data from Power BI dataset? As CSV, JSON etc. I tried to send HTTP GET to https://api.powerbi.com/beta/myorg/datasets/{dataset_id}/table/{table_name}/rows but server returned 404 error code. Thanks!...

Maximum value of datetime field

powerbi,azure-stream-analytics
Is it possible to show the Maximum of date time value in Powerbi as you can do for integer fields? I want to see in a tile last time an event received and the value for that sensor event....

Microsoft Power BI Designer data model to Excel or PowerPivot

excel,microsoft,powerpivot,powerbi
Is there a way to get a Microsoft Power BI Designer data model into Excel to work with in Powerpivot? Thanks in advance....

Opensource alternative fo azure service bus and machine learning

azure,notifications,machine-learning,azureservicebus,powerbi
I am looking for a open source alternative of below services, Azure Service Bus Azure Machine Learning Azure Notification Hubs Power BI for Office 365 Mobile Services Cloud Services Is there any solution which interoperates all these similar services as a whole solution?...

Change the color of legends in Charts in power View

powerview,powerbi,powerquery
Is it possible to change the color of the legends in Power View charts? i have been googling for some time now but could find a way. I could make use of KPIs to have an indicator as in this link: http://www.powerpivotpro.com/2012/05/kpis-in-powerpivot-v2/ I want to add similar range for legend...

How can I remove duplicate rows (in Excel) based on another column's values?

excel,powerbi
Context: I have a Excel table with N rows and 3 columns: SerialNum, DeviceID, and DateTime. DeviceID is unique (as in no values will repeat). However, there can be a 1:N relationship between SerialNum and DeviceID (so the same serial number may have multiple DeviceIDs). I want to remove the...

How to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use Power View to view, filter, and highlight my data. However I haven't figured out a way to link my table rows to the data source (i.e. tables in other tabs of the Excel spreadsheet). so that if I double-click on a row, Excel will...

Calculating PERCENTILE in DAX - SOLVED

olap,powerpivot,dax,powerbi,ssas-tabular
OK, so I have googled up a lot on this subject and keep ending up with formulas which are too damn slow. I am suspecting if I split the formula in steps(Creating calculated columns), I might see some performance gain. I have a table having some numeric columns along with...

Power BI API Limits

powerbi
The Power BI plan comparison and limits table at the URL below states maximum total data sizes limit (1GB free or 10GB paid) and maximum streaming throughput limits (10k rows per hour free or 1 million rows per hour paid). https://powerbi.com/dashboards/pricing/ Specific questions are: (1) How are the data size...

PowerBI Preview cannot refresh workbook with Azure SQL datasource

excel,powerbi
I've been working on creating workbook and share them on PowerBI Preview service, and today I found that I couldn't schedule a refreshment on my workbook. Inside this workbook, I connect with my data source(Azure SQL database) by using the Excel PowerQuery. At the moment that I add the scheduled...

How to create nested tiles in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use the tile feature in Power View to view data much more quickly. However I haven't figured out a way to have nested tiles to further drill down into the relevant data. For example, I want a tile strip at the top of my view...

Strange powerbi behaviour cannot explore dataset

powerbi,azure-stream-analytics
I am sending data from Stream Analytics to powerbi. Most of the time it works fine but from time to time I upload large amounts of data for test purposes. I notice that my SU utilization is 100% and get error messages in Stream Analytics saying that I might encounter...

How to bulk-import all Power Query workbook connections into PowerPivot?

excel,excel-2010,powerpivot,powerquery,powerbi
Within PowerPivot for Excel, under the 'Design' tab, there's an option to use 'Existing Connections' as a data source for Power Pivot. Here we can select our carefully shaped & managed Power Query connections (e.g. to SQL Server, Web pages, flat files). However this interface only permits the selection of...