If you’ve thought that, when you start using PowerPivot, will get rid of array lookup functions you were deadly wrong! Although the concept of a data model does not go hand in hand with the need to use these class of functions, it is sometimes necessary to search the arrays. To do this you will use the LOOKUPVALUE function, and how to use it, in combination with the WEEKDAY function, you will find out in the text that follows.
Recent versions of Power Pivot possess the feature that automatically generates hierarchies and columns that calculate year, quarter, and month for date columns. It is automatically activated when you drag such column into a Power Pivot report. This can be of use, but it can also bother you. In the following text you will find out how you can turn on or off this Excel feature.
In one of the earlier posts I wrote about that PowerBI comes in many “flavors”: as a Cloud Service (PowerBI.com) as a development environment (PowerBI Desktop), as application for mobile devices available on Windows, Android and iOS platforms (PowerBI Mobile). Since the majority of you who are reading these lines deal with reports development I decided to demonstrate you, in a practical example, how to use PowerBI Desktop.
Slicers are graphic objects that are used as an interactive filters for filtering reports in Pivot and Power Pivot reports. In combination with certain DAX functions they can be used for setting the parameters of the calculation. The following recipe will show you how to do a sales report in which, according to chosen exchange rate, you can see results in different currencies. To test a status of a slicer will be used function HASONEVALUE.
KPI (Key Performance Indicator) is the most common factor that shows the success of the company in any sphere of business, such as financial performance, customer confidence, employee satisfaction, process management … In Power Pivot reports KPI is obtained by comparing the existing measures with a goal value or another measure. When dragged in the report KPI is presented as a value, goal or status indicator (traffic light) showing whether the target is exceeded, realized or unrealized.