vrijdag 17 februari 2017

SSAS : Where did my measures go?

Introduction

Currently, developing a Tabular Model in SSDT and somehow I managed to let my measures disappear. What? Yep! This happened me a couple of times now. This blogpost is about the problem and how to solve it.

Situation described

Now it occured when I was entering some measures in SSDT.


Even though I don't saw the errors in the formulas, a blue mark is show in the tab. A bit annoying and you can't see why it's giving me the error. No measure with error  is shown.




Solution option 1

In the forums of Microsoft someone suggested to delete the layout file in order to get the error measures back. So I did..



When I do that, the erroneous measures are shown again but all the measures are now shown on two rows and it is completely reformatted. 


Not what I want! I have to copy and paste manually the formulas to a format that I appreciate. This is a bit labor intensive when you have a lot of measures.

Solution option 2

Now after investigating the files more closely I opened the bim file. Always make a copy of the file!


And I removed the following snippet (although the warning says that you don't want to change it manually;-) )

       
   <Command>
                  <Text>----------------------------------------------------------
-- PowerPivot measures command (do not modify manually) --
----------------------------------------------------------
CREATE MEASURE 'FactSales'[Copy of PYSalesNetRevenue]=CALCULATE([TotalSalesNetRevenue], FILTER(SAMEPERIODLASTYEAR(DimCalendar[Date]), DimCalendar[Date] &lt;= LASTNONBLANK(DimCalendar[Date], [TotalSalesNetRevenue])));
</Text>
                  <Annotations>
                    <Annotation>
                      <Name>FullName</Name>
                      <Value>Copy of PYSalesNetRevenue</Value>
                    </Annotation>
                    <Annotation>
                      <Name>Table</Name>
                      <Value>FactSales</Value>
                    </Annotation>
                  </Annotations>
                </Command>

That solved my problem. The layout of my measures stayed the same and the wrong measures were removed from the model.


Conclusion

It's possible to let measures disappear from SSDT but with editing the .bim file you can remove the measures.

Greetz,

Hennie

Geen opmerkingen:

Een reactie plaatsen