Monthly Archives

3 Articles

You will not believe these sites have been built with SharePoint

I get myself in many discussions about SharePoint as application platform for public / customer facing sites. The reason I end up in the discussion is because people are looking to use our great BI tooling to provide insight to their customers.

Most people do not believe that SharePoint is used to create internet sites (most people just know SharePoint as a collaboration platform).

Here is a great site that gives you an idea of what websites were created with SharePoint and it even features a great BI visualization of the data (PivotViewer). I encourage you to go check it out!


My personal favorite is Can you believe it has been built using SharePoint?


Power BI Pro Tip: LOOKUPVALUE() function

Power Pivot is a great way to do data modelling and analysis right in Excel. It works great for data that is dimensionally organized (facts and dimensions) as well as other forms of data. It even enables you to define relationships between datasets regardless of source. However, one thing that has been hard is the following: consider the scenario where you have stock values for certain stocks for certain days, like below:


Let’s also assume you have a table that shows marketshare (or something else) per company on a certain date, like this:

Finally, you have a third table that lists the stock label by company, like so:

Now, assume that you would like to add the stock value of a company on a certain date next to the market share for that company at that date so as to provide more context to a potential relationship between market share and stock value. Maybe a bigger market share has an impact on stock value?

Naturally, what you would do is load these tables into Power Pivot so you get the following:

Now, the next step would be to add relationships between these two tables. The relationships should be defined as follows:

I.E.: Stock labels and Market shares are related on the Company column, whereas Stock and Stock Labels are related on the Stock / Stock label column.

We can now try to get the stock value for the company at a certain date, but how? Just using RELATED() to get stock values will not work as it will return a table. You could use MAX or MIN to then get a maximum or minimum value, but that is not what we are after: we wanted to return the stock value for that company at exact that date. More generally, this problem occurs when a table is related “twice” to another table, such as monthly targets by person vs. actuals (the relationship between the actual and target table is double: both on month as well as person).

The solution is using LOOKUPVALUE() and here is how. In the Marketshares table I add a calculated column with the following definition:


=LOOKUPVALUE(Stocks[Value];Stocks[Stock];RELATED('Stock Labels'[Stock Label]);Stocks[Date];Marketshares[Date])


This might seem complex, so allow me to explain. What this does is the following:

Look up and return a value from

The Value column in the Stocks table (Stocks[Value])

For which

The stock label is equal to the stock label on record for the company (RELATED(‘Stock Labels'[Stock Label]))


The date equals the date of the market share information.

The result is:

Pretty nifty huh? Turns out that to use LOOKUPVALUE() this way you do not even have to be able to relate the lookup table to the data model at all. In my example the relationship between Stocks and Stock Labels is not even necessary, although I find it good practice to include all relationships just for clarity.

Power BI Pro Tip: DIVIDE() function

If you ever used Power Pivot to calculate things such as sales amount per capita or averages of some sort you will have run into the situation that the denominator (the column you want to divide by) is empty or zero. To cope with a potential division by zero, Power Pivot outputs Infinity. This can be seen in the screenshot below where the ‘Per Capita’ column is defined as:

=[SalesAmount]/[Numer of citizens]

Of course you can fix this by using


and others to work around the error. However, the DIVIDE() function makes this all a lot easier!

The DIVIDE() function takes two required and one optional parameters, which are: numerator, denominator and an optional value to return when division by zero occurs.

To see what DIVIDE() does, consider the following screenshot:

The Divide1 column here is defined as:

=DIVIDE([SalesAmount];[Number of citizens])

Whereas the Divide2 column contains the following function: 

=DIVIDE([SalesAmount];[Number of citizens];0)


The results are great, no Infinities are returned! By default DIVIDE() returns empty in case of a problem (Divide1 column). You can override this by specifying the third parameter so to return a fixed value in cased of a problem (Divide2 column in my example).

Hope this helps!

%d bloggers like this: