# I’m Drawing a BLANK()

Last time I started the conversation about some of the more common types of error you can encounter when developing DAX equations to define columns and measures in Power Pivot. This time, I will look at one specific type of error, the notorious and dreaded empty value in a numeric field.

Empty values in a numeric field are probably the result of a field that does not have a default value for a non-required field when new records are entered. I emphasize the non-required field because even with a default value, it is possible to blank out the default and continue entering the rest of the field values for the record before saving it. By making the field required, the user must enter something. For example, in a sales system, the quantity purchased should never be zero otherwise why would you be entering a sales record in the first place. In fact, in this case, you probably want to require that the value entered be a positive integer greater than zero.

On the other hand, suppose that you have a Discount Amount field in the record. Most of the time, the discount amount is zero, but occasionally it might be more than zero. That would be fine as long as a value was required, but suppose it is not and the user leaves the discount amount field blank. What does that really mean? Is the discount amount really zero and the user therefore just skipped over it to avoid an extra keystroke? Or did the user simply forget to enter the discount amount which is really 10%? Well, DAX will not be able to answer that question. You can only control that through defaults and required value and value validation clauses in the input form. What DAX can do is help you calculate the final sales price given the unit price, the quantity purchases and the discount amount as a percent. The following table shows an example of what a typical data file might look like.

Moving this into the Power Pivot data model, I could calculate the amount due for the record by using the following equation:

=[Sales Price]*[Sales Quantity]*(1-[Discount Amount])

DAX has no problem with this expression even with blanks in the numeric column because it treats the blank or empty values as zeros. Therefore even though the user did not enter a discount amount, DAX assumes that the discount should be 0%. You can see this in the results of the following figure which shows the calculated column Amount_Due.

However, what if our problem was a little different? What if we knew the Sales Quantity, the Amount Due and the Charge Ratio (defined as the percent of the amount actually due rather than the discount). In this case, most items would have a charge ratio of 100% and only those items on discount would have a charge ratio less than 100% as shown in the following figure.

This time when I bring this data into the Power Pivot data model and attempt to calculate the original item price for each row, I might simply want to divide the Amount Due by the Charge Ratio and then divide that amount by the Sales Quantity. My equation might look like this.

=[Amount Due]/[Charge Ratio]/[Sales Quantity]

As shown in the following figure, most of the rows calculate the item price correctly except for the two rows in which the Charge Ratio was not entered. Remember that DAX treats empty numeric values as zeros. This means that the above equation would attempt to divide the [Amount Due] value by zero resulting in the value of Infinity (yes, this is a special value in DAX rather than reporting an error or NA).

Since I do not have the ability to correct the source data before bringing it into DAX, I must use DAX to account for these missing values. To do that I can use the special value returned by the function: BLANK(). I need to test each value of [Charge Ratio] against this value of BLANK() to locate missing values and ‘modify’ the equation to treat the missing value as if it were 100% (or a value of 1.0). I can do this with DAX’s IF clause as in:

=[Amount Due]/IF([Charge Ratio]=BLANK(),1,[Charge Ratio])/[Sales Quantity]

This IF function evaluates the first expression, [Charge Ratio]=BLANK(), and returns a Boolean true or false value. If the expression returns True, the second expression in the IF() function gets returned. Otherwise, the IF() function returns the third expression which is the value [Charge Ratio]. In this case, I am simply returning a value of 1 when the Change Ratio is blank assuming that there was no discount on this item. The following table from the Pivot Data Model shows that now all of the original item prices are properly calculated.

So here are some other interesting points about using the BLANK() function. DAX evaluates any expression with BLANK() in it and returns a result of BLANK() if BLANK() is used in anything other than addition or subtraction. When BLANK() appears in an expression involving addition or subtraction, DAX effectively ignores it. An interesting exception is 0/BLANK() which returns the special value of NaN. Also in a logical expression where either TRUE or FALSE is compared to BLANK() the value of TRUE or FALSE will always will regardless of whether the logical connect is an AND (&&) or an OR (||).

Next time, I will dive into some other DAX expressions to find other types of potential data errors. By the way, the PASS Summit in Charlotte, NC is coming up real soon and I’ll be there. I will be presenting two sessions during the week. If you have chance, stop by to say ‘hello’.

C’ya next time.

By sharepointmike

# Scam or Consequences?

The other day I was pulling into a parking lot where my daughter and I were going to eat and as I was looking for a spot I noticed a real nice sporty car pull into the handicapped spot at the end of the lane I was driving in.   Out jumped (literally) a man and woman along with a child and walked into the restaurant.  They seemed to have no trouble getting out of the car and certain at the pace they made it to the restaurant door, no problem walking.  So I had to look.  Sure enough, they had a handicapped placard hanging from their rearview mirror.  To the casual observer, everything might appear normal.

In the last months of my wife’s life, we had to argue with the doctor to get a temporary handicapped placard for our car.  She could only walk with a walker and could not make steps.  How do these people get a placard and use it so indiscriminately?  More than a bit annoying.  But then some people will always try to scam the system.

I remember many years ago when I was working on a contract for EDS to create an intake system for the WIC (Women, Infants, and Children) program for the state of New Jersey that I became increasing aware of the number of people that try to scam the best intentioned systems.  The program would distribute vouchers that could be redeemed for various food items considered to be essential.  One day I went to a store that took in a lot of vouchers just to watch who would come in and how they would use the vouchers.  Sure there were lots of people who looked like they honestly needed the assistance.  However, when you see people coming in and using vouchers for the necessities with one cart and a second cart with ‘luxury’ items that they would not have otherwise been able to afford, you have to at least stop a moment to wonder whether there is a better way.

Recently there has been reports of people hiring ‘disabled’ guides who use a wheelchair to get around the parks just so they can go to the front of the line or at least through special, shorter lines.  What’s next?  Maybe they will start hiring ‘disabled’ children for the day to go with them to the parks just to avoid the lines.  Let’s hope not, but where will it end?

But it is not just our local entertainment parks like Disney, Universal, Sea World and others.  I now heard of people asking for complimentary wheel chairs at airports just to get quickly from one terminal to another without having to run and to be able to board planes before others.

This sickens me.  When we came back from Pennsylvania last November after burying my wife’s father, her cancer was getting so bad that she had trouble walking.  At our stopover in Charlotte, she asked for transportation from one gate to the other (in another terminal) because she was in such pain.  She actually felt guilty as she passed other older people walking from gate to gate while they stared at her as if to ask why does she get to ride?  In the following weeks when she was not in the hospital, she would have to stay at home all week because she could not get around by herself.  On weekends, we would take an afternoon ‘trip’ to Walmart or Target or Costco where she would use one of their motorized wheelchairs to get around the store.  It was her only chance to get out for a little fresh air.  Yet some people would look at her like she was some type of criminal riding around

There are legitimate people who really do need assistance and we should never think twice about helping them.  However, all these people who scam the system for whatever reason make it bad for everyone else.  I know it may not be politically correct and I know most people are afraid to say something to someone else who they see and who might be doing something if not illegal, at least not moral.  Yet maybe if more people started to say something, fewer people might think of such scams as having no consequences.

C’ya next time.

# Detecting Expression Errors in DAX

Earlier this week I was talking with my manager about what makes a good programmer. We discussed several factors each of which has some validity.  The one that I want to focus on today is my belief that handling errors gracefully is one trademark of a good programmer. Even programmers who are not always 100% successful at trapping all errors learn from each instance and improve their skills over time. Well if that is true of general programming in VB.NET, C# or any other language, why would you not expect the same to be true in DAX?

In this first of a multi-part series, I want to look at a few selected error types that a DAX programmer might encounter when defining expressions for columns or measures. I’ll limit the error types to syntax related errors such as missing or empty values, mixed data types and invalid arithmetic operations. Logic errors on the other hand are much more complex to identify. Often logic errors hide by generating results that may not at first be identified as wrong such as when you add two column values together rather than subtracting one from the other.

Let’s start by looking at missing values. Suppose you have a series of values in a column such as the following figure:

In this figure, you can see that Carol has no sales in 2013 and Sandra has no sales in 2010. Further suppose that for each year, management has established an expected sales goal for each salesperson and calculates their salary on a base amount plus an incentive amount. Suppose the formula they devise pays each sales person \$20,000 per year plus \$20,000 times the ratio of their sales to the sales goal of \$6,000,000. This formula could be expressed as:

=20000 + C2/6000000 * 20000

Using this formula in our table would result in the following annual salaries for each of our three sales staff.

The first thing to notice is that according to this formula, Carol would receive \$20,000 in 2013 and Sandra also received \$20,000 in 2010. In both cases, these sales staff were not even employed by the company in those years. This error may only be obvious when the Sales amount column appears next to the Salary column for each year for each sales person. Otherwise, how would someone looking at just a salary report by person and year know that a problem existed?

In this case, I might say that the expression did not check for empty sales amounts and then assign a Salary of \$0.

Missing or zero values can be more serious than just an incorrectly calculated value. Another common arithmetic error involves the division of one number by zero. When dividing a value by zero in DAX, a strict interpretation of the math should return a value of infinity. Furthermore, any number divided by infinity should return a value of 0.

Using the above data, suppose I wanted to calculate the percent growth in sales for each of the staff on a year basis. The basic formula is:

Percent Growth = ([Current year sales] – [Last Year Sales]) / [Current Year Sales]

First, we have to account for the fact that sales only go back to 2010 (2011 for Sandra). If [Last Year Sales] is zero, then dividing [Current year sales] by [Current year sales] would result in the interesting, but wrong result of 100% growth. Second, we see that the growth calculation for Carol in 2013 results in a value of #NUM! because Carol did not have any sales in 2013. Therefore this cell attempts to calculated a value by dividing a value by zero.

Finally, there is a question of how DAX automatically converts data to specific data types in expressions. The following expression attempts to add a string value of “1” to a numeric value of 1. Because the connecting operator is a plus sign, DAX attempts to convert any non-numeric value to a number before performing the operation. In this case, DAX returns a value of 2.

“1” + 1 = 2

On the other hand, if I replace the plus sign with the ampersand operator used to concatenate strings, then the result is the string value “11”.

“1” & 1 = “11”

In fact, even if I attempt to concatenate two numeric values, the result is the concatenated string.

1 & 1 = “11”

And just to round things out, adding two string values that can be converted to numbers results in a numeric value.

“1” + “1” = 2

So, in DAX, the data type is not as important as the operator used with those data types. In the following figure [Value1] and [Value2] are defined as strings and [Value3] and [Value4] are defined as numbers.

Now to make the problem more complex, DAX is column based in its operations. Therefore, if an expression between two or more columns fails because the column value could not be converted to a data type required by the operator, DAX displays an error in all the rows of the table, not just the row that has the problem. This can make finding the problem much more difficult especially if you have thousands or millions of rows.

Actually, this all or nothing approach when calculating a column is one of the main aarguments why you may want to consider using error trapping functions in DAX to handle exceptions. Next time I will take a look at some of the ways you can trap potential errors such as these and keep DAX from return bad values or errors.

C’ya next time.

# Reflections on the 7th SQL Saturday in Orlando, FL

Have you ever sat down to think about what you are doing in your career and whether what you currently do was all there was?  Do you wonder what you will be doing a year from now? Five years from now? Ten years from now?  With the rate of technology change, those are unbelievably difficult questions.  But if these thoughts have ever crossed your mind, you need to take time to answer them.  I urge those of you in Florida and even beyond to seek out the next time Andy Warren might be in your area doing his professional development presentation at a SQL Saturday or other technology conference.  He just did one this past Saturday at the Orlando SQL Saturday event and while the time was awful (7:30 on a Saturday morning), his presentation was insightful.

I will not steal his thunder, but I will say that the one thing that he makes a strong argument for is that professionals don’t just happen.  Becoming a professional in any area takes a lot of time and effort.  I once heard a quote, and I don’t know who originally said it, that to be a true expert in any field of endeavor requires a minimum of 10,000 hours.  It really does not matter if you want to be the best DBA, the best .NET developer, the best network engineer, or the best violinist or pianist, the amount of effort remains the same.  Even if you want to learn how to write better or be a better presenter, you don’t get there with a simple class and a few practice runs or half a dozen blogs.  (I’m just thinking that this is only my 250th blog post and if each one takes 2 hours to write and publish and if it took me two and a half years to get to this point, then I’m going to need about another hundred years to get it right.)

Andy also talked about the need to invest in yourself monetarily to prepare for your future.  Don’t rely solely on your company to invest in you.  After all, they will only invest in you to the extent that the investment will pay off for them and their goals.  If these are not perfectly aligned with your goals, you need to consider funding your own future.  In fact, Andy considers the need to invest not only time as mentioned in the previous paragraph but money as mentioned here to insure that you can grow into the job that you want ten years from now, even if that job doesn’t exist today.

BTW, if you want to know more about Andy, visit his blog at: http://sqlandy.com or http://www.sqlservercentral.com/blogs/andy_warren/.

Now that the Orlando SQL Saturday is over, I’m finishing my preparation for the PASS Summit in October in Charlotte, North Carolina.  This will be my second time at the PASS Summit.  I was at the one last year in Seattle.  It was a blast.  All I can say is that if you take working in SQL seriously, this is one conference that is worth attending.  If you cannot afford the full price of the conference, watch for the early bird pricing for next year’s conference probably coming out in late October or November.  Unfortunately, for those of us from the east coast, it will probably be back on the west coast next year, in Seattle.

Then in November I’ll be speaking at the Tampa BI conference on November 9th. For more information, check the website at: http://www.sqlsaturday.com/248/eventhome.aspx, especially check out the new location so you don’t end up at the wrong place.  For those of you in Central Florida, Tampa is only a little over an hour away.

After Tampa, I plan to spend some well deserved time to kick back and relax.  It has been quite a difficult year personally and I am only now crawling out from under it all.  Recently a few very good things have been going on in my personal life and I’m looking forward to a much better holiday season.  I hope to catch up with some old friends and spend some quality time with some new friends, especially one.  (After all, life is not just about SQL.  I know…Blasphemy!)  Therefore, I also plan to spend more time diving deeper into DQS (Data Quality Services) and MDS (Master Data Services) once I get past these SQL events.  These tools are absolutely essential to the BI developer to make sure that the source data used for analysis has been scrubbed.  In the meantime, I will fall back on some Excel BI related topics for this blog that I started, but did not get to finish earlier this year due to other circumstances.

C’ya next time.

# When a Crisis Does Not Deserve Immediate Action

According to some private feedback, last week’s rant apparently did not get its point across.  Just to be clear, my point was only that a president or a chief executive at a company has to be decisive, to show leadership.  Publicly passing the buck is not leadership especially after you draw a ‘red line’ to try to intimidate a country.  Notice I said publicly.  That means the president or chief executive can and should consult with his/her trusted advisors when making a major decision.  However, these meetings should occur behind the scenes.  Once a course of action has been defined, the president or chief he executive must announce the plan of action to the public (or corporate staff).

In this case, there are no clear good guys which complicates the decision.  Both sides of the conflict in Syria may have used chemical weapons.  That makes it hard to identify who deserves to be supported.  Of course, it could be argued that Obama painted America into a corner when he threatened to take action against Syria if they used chemical weapons against their own people.  Now if he does not do anything, he may appear weak.  Furthermore, Congress may back attacking Syria more because they feel they need to back their commander-in-chief in this case.  Yet by attacking Syria in support of the ‘revolutionists’, they will be sending mixed messages to the American public and world because that could look like he is supporting Jabhat Al-Nusra.  This is the same organization that the U.S. State Department called a terrorist organization just a few years ago.  So by attacking Syria, are we supporting terrorists or fighting them.  On the other hand, while the Syrian government under Assad may not be our idea of a democracy, far from it, at least they did attempt to stop other terrorist groups within Syria in the past, many of which were associated with al-Qaida.

On the third hand, if the threat to attack Syria is based mostly around their use of chemical weapons, we really do not have the jurisdiction.  The use of chemical weapons is a violation of international law.  However this law does not sanction any one country to act as a policing or enforcement organization.  Does that mean that this issue really needs to be bumped to the United Nations.  Now there is another potential problem.  The UN may not have the ability or will to step in and stop this conflict.  However, this problem may truly belong in the laps of the Security Council to decide what action, if any and by whom should be pursued.

So where does that leave us.  Well over the weekend, both the House and the Senate refused to talk with a Russian delegation to find a non-military solution to this problem.  Was this just posturing to show support for the president?  Presidents Obama and Putin however did meet, but  the only thing that came out of that meeting was that that agreed to disagree.  Not great progress, but at least they did talk.  Most recently I heard that President Obama will address the public today, September 10th.  Of course this was posted before that address, so I don’t know what will be said, if decision as been made, or if the decision is to postpone any immediate action to see if the United Nations or at least a consortium of world powers might step in to jointly act to stop the conflict.  In my opinion though, stopping the conflict is not the ultimate answer to problems in Syria.  There are obviously some root issues that must be addressed to truly defuse the situation.  Merely delaying the conflict without solving those issues may only lead to a more heated conflict the next time.  As someone once said, (and it is not really Chinese) ‘May you live in interesting times.’  I’m just not sure if that is a wish, a threat, a promise, a warning, or something else.

Now for some lighter news.  This coming Saturday is the 7th annual SQL Saturday here in Orlando.  I will be speaking there about Data Quality Services and hope some of you can make it to the event, if not may session.  SQL Saturday’s started here in Orlando back in 2007.  Today, they are international.  We are also hosting several pre-conference sessions starting Wednesday with two more on Thursday and Friday.  Except for Wednesday, these training sessions due have a cost associated with them, but the cost is minimal considering the quality of the speakers we have talked into coming to Orlando a few days early.  Check out the Orlando SQL Saturday event at: http://sqlsaturday.com/232/eventhome.aspx.  We are expecting a big crowd, so make sure that your register so we can properly plan the day.

C’ya next time (or this Saturday in Orlando).

# Subscribing to SharePoint Libraries with RSS

Do you have libraries in SharePoint that you need to monitor for additions and perhaps even changes? I know that if you are checking these libraries manually, that over time, you will occasionally forget especially if you are as busy as I am. One way to receive notifications automatically is by setting up alerts within SharePoint. Alerts are a great tool that can send you an email when the contents of a library or list has been updated. You even have options that determine what types of changes you want to be notified about. Alerts can be scheduled to arrive instantly, daily, or monthly. With daily alerts, you can even specify when during the day you want to receive the notice and for weekly alerts you can specify not only the time, but also the day of the week.

First I have to make sure that RSS has been enabled for my site. My SharePoint administrator or I can do this by going to Site Settings of the site from which I want to create the feed and within the Site Administration group of options, click RSS.

The RSS options appear in the following figure and include the capability of adding a copyright notice, editor and webmaster names and a frequency of pushing out updates. By default, SharePoint recommends sending out RSS updates only once every hour (60 minutes). However, the most important property is the check box at the top of this figure that allows me to enable and disable RSS feeds for the current site.

Next I would open the library or list from which I want to receive the feed. Let’s assume for this article that I am creating a RSS feed from a library (the steps are similar to those for a SharePoint list). From the Library ribbon as shown in the following figure, find and click the RSS Feed option in the Share & Track group.

A page appears with some basic information about what an RSS Feed is and includes a link to subscribe to this feed. While the text refers to a generic RSS reader, the default is usually your email application such as Microsoft word.

Because the server application (SharePoint) is trying to open and use a program on my computer (Outlook) a dialog appears as part of the security features of the operating system asking if I really want to allow the website to open that program. This action is normal and should be allowed as long as I know and trust the URL that is trying to make the connection.

After opening Outlook, Sharepoint tries to add the RSS Feed to Outlook. Again, security within Outlook requires permission for another program to add a subscription within Outlook.

While there are some advanced options when creating a RSS feed to Outlook, most of the time, I can safely ignore these options and just click the Yes button as shown in the above image. However, for future reference, you may want to know just what those advanced options are just in case you might need them. So the following figure shows the advanced options.

The feed name is generally a concatenation of the site name and the library or list name with a colon separating the two. I can change the text the user sees by changing the Feed Name in this dialog. Note that this has no effect on the feed source. I also see a location reference. Notice that the location URL contains a GUID to uniquely identify the library or list. If I delete and recreate the source library or list, the new version gets a new GUID. Therefore, any RSS Feeds pointing to the old location will fail and the location URL must be regenerated.

It is also possible to change the folder in which Outlook saves the RSS feed.

Finally, by default, Outlook only queries the RSS feed source once every hour. Therefore notification of additions or changes do not occur immediately, but do occur within at most 60 minutes.

After clicking OK, Outlook creates the RSS Feed in the Folders section of Outlook as shown in the image below.

When I click on the folder representing my feed, the center section of Outlook displays the items in the feed much like the Mail section of Outlook displays individual emails. To read an article from the feed, I double click on it. I can also easily delete an article from your Outlook feed. Note that deleting the article from my local feed does not delete the source article in SharePoint so I can feel free to get rid of the articles I have already read.

In addition to displaying the feed in Outlook, I can have the feed appear in both Outlook and IE using what is called the Common Feed list. I can get to this setting from Outlook’s properties. On the File submenu and select Options as shown in the following image.

From the Outlook Options dialog, I click the Advanced group of option in the left navigation. Then in the right panel, I scroll down to the RSS Feeds section. Click check box before the option: Synchronize RSS Feeds to the Common Feed List (CFL) in Windows. I may also want to check the option to redisplay as new any RSS feed item that has been modified.

After saving my property changes in Outlook, all RSS feeds that I subscribe to in the future will appear in both Outlook and IE under the Feeds tab of the View Favorites dropdown. To open a feed, I click on the feed name. Note, adding feeds to the CFL will not affect existing feeds already in Outlook.

That’s all for today. C’ya next time.

By sharepointmike

# Do or Not Do…There Is No Try

For those of you who are Star Wars fans, and I know you are out there, the title of this article is actually a quote from Yoda, but it seems appropriate to the current world situation.  Should we or should we not intervene in Syria?

The situation in Syria has been deteriorating for some time.  I will not here try to debate which site is right or wrong.  Rather I want to examine the dilemma it puts President Obama in.  In case you don’t remember, when Obama ran for president the first time, he argued strongly that America needed to get out of the Middle East.  He wanted to pull all troops out of Iraq on the basis that they never found any weapons of mass destruction in Iran.  He also has been strongly pushing to pull all of our troops out of Afghanistan.  Now he may be contemplating military action against Syria because it is claimed that there is credible evidence that Syria has used chemical warfare against the rebels and the citizens of the country.

To put this one point in perspective, at the time that then President Bush decided to attack Iraq, there was also ‘credible’ evidence that Iraq had and may have used weapons of mass destruction against its own people as well.  Whether those weapons were ever found or as some conspiracy theories suggest that the weapons were ‘relocated’ outside of the country may never be known for sure.  Care to guess to which country they may have moved their weapons of mass destruction?  If you guessed Syria you win the prize.

So how much evidence is needed to justify the United States and its allies to send in troops?  And what happens if that evidence is moved out of the county or destroyed.  Will the next U.S. President condemn the actions of Obama much like Obama condemned the actions of Bush or did Obama find a way out?

So I understand that making a decision to attack another country is a difficult task.  Countless lives could be lost or saved depending on the success or failure of the action.  No matter what you do, people will second guess you.  On the other hand, ignoring the actions of a country like Syria or Iraq and hoping that they will come to their senses is not a good choice either.  Look back to the start of WWII.  While Hitler was taking over neighboring countries and sending Jews to concentration camps, the United States sat back and watched for a long time hoping that with each military action, Hitler would be satisfied and stop trying to take over more neighboring countries.

Well, hope is not a plan.  Hope is a dream.  A dream without a strategic plan on how to accomplish that dream is just a fantasy.  So now may be the time for the president to put forth a plan of action.  After all, he is the commander-in-chief of our armed forces.  More than forcing government run insurance down everyone’s throat, more than increasing the government’s ability to spy on and interfere in the lives of our citizens, and more than apologizing to every other country about the ‘bad’ things that the United States has or is doing, this ability to send in troops is something the Commander-in-Chief can do on his own.

But what has he done.  While condemning the Syrian government and its use of chemical weapons, he has passed the ball back to Congress to decide.  Perhaps he is hoping that Congress will again show their inability to act on anything (like getting a budget passed).  Then Obama could always come back and say that Congress was at fault for not acting if later the situation worsens and the American public begins to wonder why we did not step in to prevent further bloodshed.  On the other hand, if Congress does vote on attacking Syria and those weapons of mass destruction are never found, Obama could always say that it wasn’t his fault.   He did not authorize the attack, Congress did.

This is where leadership is needed if a country is to remain a world power.  Sitting back and watching from the sidelines may feel safe, but it also means that you are not in control.  It’s time to get into the game Mr. President.  Make your own decision.  Own your decision.  But once you get into the game remember to play to win, not play to a draw and then walk off the field.

Yoda said, ‘Do or not do.’  That is what a true leader lives by.

C’ya next time.