Opportunity =Money! = Worth

We have always had to measure the worth of things. Everybody has things they want and in order to get them they need to trade. With trade came the need to ensure you’re not being ripped off and so we standardized the rates. Initially this meant a fixed quantity of food or cattle gets exchanged….

PowerBI DAX – VAR

A very useful way to work with DAX functions is to use VAR. VAR is a form of variable in which results can be stored temporarily without having to create measures and then using them in others. For example you use Var to store the result of a particular group and then use the var…

PowerBI DAX- USERNAME

This is a function that may not be used as frequently as it used to be but it is likely you will encounter it in older PowerBI Reports where RLS or row level security was implemented. The function returns the username of the user currently viewing the report. Using this information and mapping it against…

PowerBI DAX – USERELATIONSHIP

A common issue faced when working with PowerBI is the limitation of having only one Active relationship between two tables. While this is a not an issues when importing data it can make creating measures a hassle. An example of this scenario could be the use of calendar table for Order Date and Delivery Date….

PowerBI DAX- TREATAS

Once we are able to create virtual tables within PowerBI using Functions such as EVALUATE , CALCULATE TABLE etc it might be required to filter the contents of the virtual table based on certain Input criteria. In such cases we can use the TREATAS function to apply filters to a Virtual or Actual Table as…

PowerBI DAX- TOTALYTD

A very useful kind of report is called a cumulative report. Which is based on summarizing data cumulatively. This means we can see the area chart as progressively growing as the year goes by. Charts such as these provide a clear indication of the long term trajectory of the sales and clearly indicates Sales for…

PowerBI DAX – TOPN

Another commonly used function is the TOPN function. It is used for both TOP and BOTTOM use cases. In this example to demonstrate the basics we are first going to simply fetch the top 34employees by their employee key. The below Formula shows what this would look like Table = TOPN(4,ALL(‘Dimension Employee’),’Dimension Employee'[Employee Key],ASC) To…

PowerBI DAX – SWITCH

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…

PowerBI DAX – SUMMARIZE

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…

PowerBI DAX – SUM and SUMX

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…

PowerBI DAX- SUBSTITUTE

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…

PowerBI DAX – SAMEPERIODLASTYEAR

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…

PowerBI DAX – ROUND

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…

PowerBI DAX – RANKX

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…