You have a huge table with many date entries in it. You need to find out which date is after 20th week of the year or if a certain date is Friday. MS Excel will help by three functions – WEEKNUM, ISOWEEKNUM,WEEKDAY . It is definitely more effective than searching in calendar. More so if… Read More »
FILTER functions (filtering functions) are used to get values from the table. Some functions enable additional filtering in a given table. These functions are often part of different functions as is nested parameter and enable dynamic calculations. They often serve as a value filter for function CALCULATE. Mostly it is the case of typical member… Read More »
Aggregate functions in DAX language are used to get certain summarized or grouped data view. Thanks to this, we can edit high detail data in a certain column into more simple and reportable form using aggregation. We can for example sum up (SUM, SUMX) some financial metrics by individual category or express maximal/minimal (MAX, MAXX,… Read More »
CALCULATE is an important function for a large number of calculations and for those of you who actively do DAX formulas in Power BI or PowerPivot. Understanding of how the function works and how it is used does not look simple at first glance. And that is why this article is here. Syntax function: CALCULATE(… Read More »
Function REALTED is very frequent and useful in DAX language. We can use it for example when we need to search table (A) for value from other table (B). And finding the according value is the exact task of this function. Syntax is very simple: How Come RELATED Function Needs Only 1 Parameter? Data organization… Read More »
What about upgrading GROUP BY clause by use of useful commands? GROUP BY is used in a clause with aggregate operations. When it is used, aggregation happens throughout all columns. Using this simple “grouping” does not enable us to do totals and subtotals. But there is plenty of operators which can easily query for totals.… Read More »
You probably noticed that there are many functions in DAX that are similar to Excel ones (as in article here). Contrary to Excel functions, DAX haves on strange thing. Some aggregate functions have another similar function ending with X – for example SUM and SUMX or COUNT and COUNTX. These functions often give same results… Read More »
Correct table indexing in SQL Server is a base for good database performance during querying. You need to understand how SQL Server stores data into tables/indexes if you want to create appropriate sql indexes. It is also important to know how to approach these data correctly during querying. How Does SQL Server Organize Data Physically?… Read More »
We can create two types of new values using DAX in Power BI or PowerPivot. They are calculated measures and columns. Difference between measure and column and its understanding is another major step in order to understand DAX language for Power BI. New measure or column in Power BI is added by clicking right mouse… Read More »
DAX language (Data Analysis Expressions) servers for data management and editing prior reporting in Power BI or Powerpivot, similarly to function set in Excel. This language is able to return values based on use of functions. We can either enrich, add or analyze data prior reporting in Excel, and DAX enables us to do the same.… Read More »
We newly added section Business intelligence E-books. You can find freely available education materials here. The section is accessible via menu or by link https://biportal.cz/en/e-books/
DAX language haves its standards as does any other language. This article sums up and describes how look the parameters of formulas. In other words, this article is about function arguments overview. It is good to know parameter categories appearing in this language to effectively use parameters in DAX. What is Function Parameter in DAX?… Read More »
What I have here today is a article for beginners. It will be full of sql query examples. We will start from the most basic ones and continue up. SQL queries will be organized chronologically according to their difficulty. I will add more later on. SQL knowledge is a must for any IT department nowadays.… Read More »
This article will tell you about creating ER diagrams in SQL Server management studio. ER diagrams (entity relationship diagrams) describe static structure of database tables. Just like when building a house, the construction master needs the project documents, database specialists also need their ERD to build the database on strong foundation. ERD is part of… Read More »
This article is made especially for those who regularly export large data volumes to Excel in order to analyze them or process some kind of regular report. Usually, we insert the data into Excel simply by copying it from some factory system. But data can also be inserted into excel in more suitable ways which… Read More »
I must say right from the beginning that even though this process is possible, I do not recommend it (but I will show it to you anyway) :). Launching SQL queries in Excel is not optimal in general, but sometimes there is no other option. This guide is aimed on advanced Excel users with SQL… Read More »
SQL Error “Login failed for user <user name>. The user is not associated with a trusted SQL Server connection” is quite common. The problem is that SQL Server cannot identify user name. This is how the error message looks like. What are the Most Common Reasons? Incorrect login or authentication type (Windows vs. SQL) Login… Read More »
What I have for you here today is a different kind of stuff. We will take a look at what should BI developer working on Microsoft technologies be able to do. I put up together a list of knowledge and abilities which I consider important for this job. I gave each skill rating (1-10) according… Read More »
Import of Excel sheet or csv file into SQL Server is a quite common task. You can find a guide on how to do it below. Let’s have an excel file containing data on companies: Steplist – Import Excel to SQL Database Example 1) Data import… Read More »
When working with database objects, we may sometimes get into a situation when we want to change property of an object. For example change of data type in column of a table via table design in SQL Server management studio. If there is data in the table, SQL server should display an error message if… Read More »