Option to create a new measure under fields(...elipses) is not shown in Power BI service

[SureshR]

Hello Team,

Can i create a new measure in Power BI service, i know we can create in power BI desktop but i want to create in service.

What are the options available to write DAX (Formula) in Power BI service.

Thanks

Suresh Ramadurgam

[Seth_C_Bauer]

@SureshR There are none currently. There is no way to add DAX measures or calculated columns in the Service.

[SureshR]

Thank you @Seth_C_Bauer

If in Power Bi service i cannot craete a measure, then is there a way that my desktop PBI file (Which has one measure (DAX written) )  and its datasource (desktop : Excelfile) can be uploaded via sharepoint to Power BI  service. I want to place my worksheet & pbi file in sharepoint, but when ever there is a delta i download file from sharepoint to desktop add these new rows to file  and again upload back to sharepoint. But how does the pbi file refreshes in power BI .

Hope i have articulated my problem well, if not please revert.

Best Regards

Suresh R

[Seth_C_Bauer]

@SureshR The normal process flow would look something like this:

You would install a gateway if your data source is on premises.

You create your report in the Desktop. You publish that to the Service. At that point, the dataset is in the Service.

The dataset can have a scheduled refresh set up to 8x per day, and will check the source file in your sharepoint location for any updated data.

In essence, all the refreshing tools and capabilities are in the Service. If your SharePoint is online, than you don't need a gateway as everything is in the cloud.

Any additional changes to the report, can be done in the source Desktop file, and republished to the Service. This will update the dataset that you have deployed and will not disrupt any refreshing or sharing that you have on the existing dataset.

Hope that answers your Q

[SureshR]

@Seth_C_Bauer please help sorry to bother with my long messages.

[Seth_C_Bauer]

@SureshR A couple things here. First, it looks like you need to get your gateways working for any on-premises connections. The easiest and most straightforward approach would be using your local files, as long as a gateway is running. 

If you do go the SharePoint Online route, look at this tutorial to make sure you have set the path up appropriately in the Power BI Desktop. See here. Then upon deployment, you should have access. I say "Should" because I haven't utilized this source myself yet.

Is there some reason your gateways are offline? That alone should be resolved just so you don't have these issues in the future.

[SureshR]

@Seth_C_Bauer Really Appreciate your timely response, I followed the link as suggested by you. But that is entirely different from what i am doing.In the article he is first placing the file in sharepoint folder and creating a report from scratch in Power BI service.But in my case i have excel worksheet  in my desktop and careted a power BI report using Desktop client.I published that report in service. But when the same file i keep in sharepoint how do i schedule refresh fom the dataset to this excel in sharepoint. 

how do i set up gateway for sharepoint because i put my excel in sharepoint for automatic refresh?Rather Steps to create a data gateway for sharepoint folder.can you please take some time and explain i am totally naive to this side of settings.Pic attached .

I have tried similar to what you have shared earlier like connecting from sharepoint folder and creating report in service directly but problem is i cannot create a measure using the service, which is why i am dependent on Power BI  Desktop tool for report.

Thanks

Suresh Ramadurgam

[SureshR]

@Seth_C_Bauer 

[Seth_C_Bauer]

@SureshR The link I provided describes how to connect to an Excel file in a SharePoint Online folder from the Power BI Desktop... 

Your first issue is to get your gateways up and running. When you installed them on the machine, was there any issues?

After you have the set up, we'll need to walk step by step through the process.

Your Excel file location in the Desktop needs to match where the file currently exists. Using SharePoint, my understanding is either you need to use the new SharePoint Folder connection, or use the Web connection. The Web connection is going to require that your personal gateway is online and working.

[SureshR]

@Seth_C_Bauer sincere aplogies not getting back to you on time.As per your suggestion i have put my Excel on sharepoint folder and  I have synced sharepoint library to my laptop. From power bi desktop i tried conencting using folder option in GET DATA and can able to create a report of that excel in sharepoint folder. Also installed personal gateway on my laptop and credentials given as windows authentication. Evrything seems to work fine . Now the problem is the report has to be built from scratch again for the file in sharepoint filder eben though it is the same file on desktopused for building the report.is tehre anyway that i can use sharepoint folder for this report.

[Seth_C_Bauer · Accepted solution]

@SureshR As long as the file is the same you should be able to change the connection.

For the report you already built. Save a copy, so you don't accidently destroy what you have.

Open the report, go to edit queries. In here, click on the advanced editor to see the M code. My guess is that you will need to directly copy the m code from the "working" file, to this one. This should replace the dataset in the report file. I haven't tested out your direct scenario, but you should be able to replace the datasource with the new one. The gotcha, is if you have applied steps, you would need to add those... (This is hypothetical, as I think this "Should" work, but haven't tested)

The long and short, is you can change that connection, I just don't know if there are any restrictions off the top of my head to making big connection changes. In any case, make sure you do this on a copy so you don't permenantly mess up the original file.

[SureshR]

PBI file and Worksheet on my desktop (This worksheet is downloaded from another portal and made some manipulations for few columns)

PBI file when published to power BI service it has seen in all places below

1. As a dataset,2.As a Report3.As a Dashboard when pinned from report

After reading your reply today I have placed my excel worksheet in SharePoint folder.

Since my pbi file is already shared to service,  now I want to make this refresh automatic whenever new data comes in another portal  I download this file from sharepoint add few rows and again place it back in sharepoint.

I know you have said that sharepoint online does need gateway. But how do I make this connection to excel in sharepoint from newly created dataset in service.Whilst I am writing this I have tried as I type , but no clue how to proceed

Am I missing anything here?Or do I need to set my gateway here first i have got no clue.please see my screenshots.