cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yokofujimoto Frequent Visitor
Frequent Visitor

Power Bi publisher add in - connect to data

Hi I am trying to connect to PowerBI report, using Power BI publisher add-in on excel. 

I am choosing 'report' not dataset, but it seems to inporting dataset. 

Steps I took 

1. Create a table on desktop powerBI (From the whole data set, I selected few measures and dimentions, which I need)
2. Publish to my report
3. on Excel - click connect to data and chose report
4. I got the whole dataset in pivot and got a pop up saying this dataset doesnt have a measure. 

 

Hope you could tell us - where I am doing wrong. 

 

Thanks 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto ,

 

the connection from excel to Power BI is always built up to the dataset. That's right.

https://docs.microsoft.com/en-us/power-bi/publisher-for-excel#connect-to-data-in-power-bi

Dataset must have measures - The dataset must have model measures defined in order for Excel to treat the measures as values in PivotTables, and to correctly analyze the data. Learn more about measures.

Look at this.

https://www.youtube.com/watch?v=enfY74xSN58

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

View solution in original post

mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto ,

 

I think I know why it is.

You are connected with the dataset from your IT and your measure is part of your report. But Excel will connect directly to the dataset.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

View solution in original post

7 REPLIES 7
Highlighted
mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto ,

 

the connection from excel to Power BI is always built up to the dataset. That's right.

https://docs.microsoft.com/en-us/power-bi/publisher-for-excel#connect-to-data-in-power-bi

Dataset must have measures - The dataset must have model measures defined in order for Excel to treat the measures as values in PivotTables, and to correctly analyze the data. Learn more about measures.

Look at this.

https://www.youtube.com/watch?v=enfY74xSN58

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

View solution in original post

yokofujimoto Frequent Visitor
Frequent Visitor

Re: Power Bi publisher add in - connect to data

Thank you for your reply! @mwegener 

"the connection from excel to Power BI is always built up to the dataset. That's right"
One thing that really confused me is this option - even if I chose report, which is in a table format that contains meausre, it feeds data set to the pivot table. 

 

The other thing I do not understand is - dataset has measure but the pivot says it does not contain any measure. 

(I know the difference between measurea and data dimension:))

Can you think of any reason? 

mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto 

yes i think the option is confusing, but i think this option should guide the user to the right dataset.

 

Could you share a screenhot from you dataset and mark you measure?

 

 

yokofujimoto Frequent Visitor
Frequent Visitor

Re: Power Bi publisher add in - connect to data

@mwegener 

Thank you for following up on this. 
On data set, I have these, both on desktop and published report. 

Actually those are the same data. The one with a sigma sign are pre-set and provided by the admin and just because it did not load on the pivot table, I created as a new mesure using sum formula. 

 

Capture-PowerBI.JPG

However, I connect it on the pivot table - it will show up as dimension, which I cannot calculate and I cannot also see the measure I created (the one with a calculator symbol in power BI) anymore in excel pivot. 

Capture-excel.JPG

 

I hope there is a way that I can import the measure correctly...

Thanks

mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto ,

 

your measure should be on the top in a separate section with a sigma sign (the one with a calculator symbol in power BI).

 

Excel_Pivot_PowerBI.png

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

yokofujimoto Frequent Visitor
Frequent Visitor

Re: Power Bi publisher add in - connect to data

@mwegener  
That's the mystery I have been facing.
I do not see the separate section and the measures I created is also not showing up in the pivot table, although I see them in Power BI (browser) after I publish them. 

Thanks 

 

Yoko

mwegener Super Contributor
Super Contributor

Re: Power Bi publisher add in - connect to data

Hi @yokofujimoto ,

 

I think I know why it is.

You are connected with the dataset from your IT and your measure is part of your report. But Excel will connect directly to the dataset.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)