cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Difference in Fact fields Available - OLAP in Excel Vs PBIX

Hi,

 

Quite new to SQL / direct connections - I have a Fact table in an Azure Analysis services SQL which shows more fields in PBI than it does when I open the same connection in excel.

 

It appears that the aggregate values show in PBI but only the calculations show in excel..is this normal? see screenshots... thanks in advance.

 

ExcelExcel PBIPBI

 

 

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Difference in Fact fields Available - OLAP in Excel Vs PBIX

Hi @rh203 ,

Based on my test, I find that in Excel, the measure will show like below

701.PNG

and in powerbi, it will show like below

702.PNG

You could expand them, then compare it to see whether  measures are missing or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
New Member

Re: Difference in Fact fields Available - OLAP in Excel Vs PBIX

Hi Zoe,

 

Thanks for the response - I think I should have explained I am working with data stored in an Azure Analytics Server so not sure if this makes any difference.

 

I have found the link below but even when I try to create a measure directly in the FACT table in PBIX and then pubish to the PBI server and then analyse via excel, the result is the same... most of the key figures which are showing in PBI as measures (with the sum icon) do not show in excel as measures.

 

Microsoft Help Doc 

 

Perhaps its something to do with how the data has been staged in the SQL in the Azure Server Vs. excel Vs. PBI but either way I find it very strange that Excel is viewing the majority of the KFs as dimensions vs measures / key figure data which can be aggregated?

 

Thanks

Ryan

 

 

Highlighted
New Member

Re: Difference in Fact fields Available - OLAP in Excel Vs PBIX

If any use, this is how the SQL model shows up when entering the server via excel directly... can clearly see excel recognising the majority of the fact table as values Vs. dimensions..

 

Annotation.png

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.