Tag Archives: Business Intelligence

PowerBI DAX – SWITCH

By | June 16, 2020

Switch is a DAX function that is used frequently to create custom groups and classifications. In this example we use Switch to create a column called approval where we code different types of approval for the total sum of Amount. You can use switch to replace multiple values with another value instead of using IF… Read More »

PowerBI DAX – SUMMARIZE

By | June 16, 2020

A useful function when measures need to be created on top of summarized data the SUMAMRIZE Function as its name suggests returns a table containing aggregates based on some grouping. In this example we can see a Table created which summarizes the SaleAmount from the Transactions Table Grouped by the year and the CustomerKey. Basically… Read More »

PowerBI DAX – SUM and SUMX

By | June 16, 2020

In this post we cover the difference between the DAX Formula SUM and SUMX. If you are even somewhat familiar with EXCEL you already know what SUM does. SUM aggregates the values over a column taking all rows into account. SUMX performs the same aggregate operation but over a subset of the data. Basically when… Read More »

PowerBI DAX- SUBSTITUTE

By | June 16, 2020

The DAX Function SUBSTITUTE can be treated like the Replace function in MSSQL Server. It replaces a string with another string. Its syntax is also similar to the syntax of the Replace function in SQL Server. If we look at the example from the previous post we can see that CONCATENATE has resulted in the… Read More »

PowerBI DAX – SAMEPERIODLASTYEAR

By | June 16, 2020

In the previous post we saw the shortcomings of using ParallelPeriod DAX function when it comes to summarizing data against a different dimension attribute. This problem can be overcome by using the DAX Formula SAMEPERIODLASTYEAR. Notice how this function can retrieve results for the same period of the previous year but won’t be able to… Read More »

PowerBI DAX – ROUND

By | June 16, 2020

Often the result of a calculation will have decimal places. However it is may not be required to show the decimal places at the time of visualization. In such cases you can use ROUND to truncate the output to the required number of decimal places. This can also be achieved in a number of other… Read More »

PowerBI DAX – RANKX

By | June 16, 2020

In our previous post we saw how to identify the top N Sales Persons a common follow up to this task it to rank the Sales Persons as well. In this post we explore how to RANK the Sales person based on the total Sales Amount. Ranks = RANKX(ALL(‘Dimension Employee'[Employee]),SUMX(RELATEDTABLE(‘Fact Order’),’Fact Order'[Total Including Tax])) The… Read More »

PowerBI DAX – Percentile.Ex

By | June 16, 2020

Percentile is a great way to find distribution of data across your range of values. It give a great feel for how the data is distributed and is especially useful for analysis over demographics etc. Percentile is an indication of how what is the upper bound for the value at a particular percentile value. Consider… Read More »

PowerBI DAX – PARALLELPERIOD

By | June 16, 2020

Often in Business Intelligence developers are required to create Measures based on time series analytics like compare this year sales with last years. In such cases you can use the Parallel Period Measure to aggregate the data based on dates in the calendar. This is especially useful when trying to identify trends such as increase… Read More »

PowerBI DAX – ISBLANK

By | June 16, 2020

A common issue with database sources is the presence of NULL values in column. When this data is imported into PowerBI it can often skew the report and graphs. It becomes important to identify such null values and handle them when reporting. Keep in mind Blank mean Null and not ” or #N/A or some… Read More »