I had a rather odd experience this week. We are rolling Power BI out in the company I work for. We have a big investment in SSAS MD and that is not about to change. So I have been creating reports on top of SSAS MD for the last month or two with reasonable success. We are starting with a single cube and from the beginning number formatting was not recognized by Power BI. I assumed this was one of many things Power BI didn’t do well from SSAS MD so we tried to work our way around the problem. We used VBA functions in the cube script to limit measures to single decimal and we multiplied ratios by 100 to show percent. This was ok but not very good. Then we found a bad side effect with the VBA function which was that Excel pivot tables couldn’t recognize empty cells when using measures formatted with those functions. So I really starting looking into if Power BI couldn’t do number formatting from SSAS MD. It turns out that Power BI can do it but it just didn’t work for us. I tried few of our other cubes and while it didn’t work in the first couple cubes it did in some others.
I got a tip from Koen Verbeeck (twitter) about an article by Chris Webb (twitter) (article) explaining that while Power BI did recognize number formatting from SSAS MD if you changed the number format in a scope sentence Power BI wouldn’t recognize it any more. I read the article but couldn’t link that to our problem as we didn’t change any number format in scope sentences and not only some of our measures were missing number formatting, but all of them.
But the article got me thinking if our script could be the problem. We had quite few scope sentences in our script so I decided to start by deleting all scope sentences from the cube and see if Power BI recognized the number formatting then. And to my surprise it worked. I then decided to put them all in one by one to see where it went wrong. After some back and forth I managed to conclude that our time intelligence script (DateTool by SQLBI) and one other custom scope sentence (working on a dummy dimension) where to blame. Both these scripts were at the bottom of the script. What did the trick in the end was to take these two scripts and move them above the measures and all of a sudden Power BI recognizes the number formatting.
I now have the task of removing all the extra measures from the cube and change all the reports to use the normal measures instead of the special measures we had created to go around the problem.
If you are using Power BI on top of SSAS MD and don’t get the number formatting through. Take a hard look at your scope sentences and see if you need to either change them or move them around in your script.
Is your SAAS MD in Azure or are you using a gateway? Curious how using Power BI as a thin reporting client performs vs having the model inside Power BI.
My SSAS is on prem using the Gateway. The performance is not as good as with the model inside Power BI. It’s not bad but there can be a little lag when the page loads sometimes. We had already created this particular cube and it’s being used in Excel as well so re-creating it inside Power BI would not only mean a lot of work (this is a very complicated cube) but we would also have to maintain two different models to accommodate Excel users. For new cubes I suspect we will create tabular cubes on premise and only do pure Power BI models for smaller Power BI only models or proof of concept that later can be migrated to SSAS tabular.
Thanks for your research. I can now try somethings to get our cubes working with formatting problems.
Hi Thomas
No problems. Hope you manage to fix yours. The main culprit seems to be the Date Tool from SQL BI but here might be other scope statements. My best advice is to just remove the whole script and then start putting calculations back in to it one by one until it breaks. Then move that statement to the top of the script and try again.