Difference in Fact fields Available - OLAP in Excel Vs PBIX

[rh203]

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.

Excel PBI

[dax]

Hi @rh203 ,

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

and in powerbi, it will show like below

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.

[rh203]

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

[rh203]

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