Power Bi publisher add in - connect to data

[Anonymous]

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 

[mwegener · Accepted solution]

Hi @Anonymous ,

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.

[mwegener · Accepted solution]

Hi @Anonymous ,

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.

[Anonymous]

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]

Hi @Anonymous 

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?

[Anonymous]

@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. 

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. 

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

Thanks

[mwegener]

Hi @Anonymous ,

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

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

Please give Kudos for support.

[Anonymous]

@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 · Accepted solution]

Hi @Anonymous ,

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.