Author Archives: Ing. Jan Zedníček - BI Developer, Finance controller

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

WEEKNUM, ISOWEEKNUM,WEEKDAY Excel – Was it on Friday After 15th Week of The Year?

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 for Power BI in DAX – Overview

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 »

Aggregation in DAX – How Aggregations Work? + Function Overview

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 »

Function DAX CALCULATE – Aggregation With a Condition in Power BI

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 »

DAX RELATED Function for Power BI – Lookup Into Table

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 »

SQL ROLLUP, CUBE, GROUPING SETS – Totals and Subtotals

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 »

DAX | SUM and SUMX – How to Perform Aggregation in DAX Language for Power BI

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 »

SQL Indexes – Indexing Theory Simply, Balanced Tree, Heaps

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 »

DAX Difference Between Measure and Column + Example on Report

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 – Introduction into DAX for Power BI and Powerpivot

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 »

Parameters in DAX – Introduction into DAX Expressions

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 »

Basic SQL Queries – Overview For Beginners With Examples

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 »

SQL Server ER Diagrams – Do Not Expect Miracles

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 »

How to Execute SQL Query in Excel (Office 365)? Tutorial with Examples

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 »

How to Execute SQL Procedure in Excel incl. Parameters with Example

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 – What to do?

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 »

MS BI Developer Skill Set – List of Knowledge and Abilities

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 »

SQL Error: Saving Changes is Not Permitted – Solution with Example

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 »