We can domany amazing things through DAX functions, this time I want to share the DAX formula about how to calculate the distance between two locations. And based on this, wecan apply it in many advanced scenarios.
We all write our codes in a proper format so that readability and understanding of codes become easy. While working with POWER BI, we use DAX Expressions to write our measures and calculated columns. We know that formatting is not required in DAX. It’s normal to see DAX expressions that span over 10 to 20 -lines or more, depending on the complexity and requirements. Sometimes we ignore DAX format as it is time-consuming, and we need to press ALT+ENTER for the new line, which is out of habit for all of us. There is too much ambiguity, and a lack of official specification makes it even harder to comprehend. Whenever I am working with Power BI, I use the following technique to measure it.
When calculating in the date dimension, sometimes we only want to calculate the Data in workday. In that case, we will need to get rid of holidays or weekends. This blog will show you how to create a calendar table and mark the holidays, weekends and workday.
I've always done this before about using slicers to display different measures in graph. Create several measures and create a disconnected table to use as slicer. However, this method has the limitation of data format. Since all measures are encapsulated in a selection measure, only one data type is allowed. This article will show you another way to create dynamic charts based on the external tools (Tabular Editor) updated in July 2020.
Since June 2020 a new feature has been introduced in DAX Studio https://daxstudio.org/ that allows you to design queries using the UI, earlier you would have to write code from scratch or import extract of Performance Analyzer from Power BI. Query Builder is a great add-on to this tool
As shown in the following sample table, when we want to get all the values of the ID with the latest start time, we need to filter twice to get the result. Ex: The Dataset ID (A) contains the latest Start Time. Now we want to get all rows which contain Dataset ID A. We can see Dataset ID A has two rows. If we only filter the latest Start Time, we can only get one row of A. Therefore, we first get the dataset ID based on the latest start time, and then get all the values by filtering the previous obtained dataset ID(A). If you want to get result in the example table below, you can use Power Query or Dax.
The default display style of Power BI (whether it is a table or a matrix) is nothing more than interlaced display. Such a layout cannot help us intuitively find the details of each country at a glance. The conditional format in PowerBI can help us solve this kind of problem very well.
This article uses a simple example to briefly introduce how to change the interlaced background style of the table/matrix based on measures and custom columns.
Context transition - one of the most complex topics to understand and master but have you ever wondered, what actually happens behind the scenes? To be honest at least for me the behind the scene stuff is much more clear and easy to understand and here is why.
We can use measures to have an easy comparison on the value of today and yesterday, current month and last month. However, it isn’t easy when we encounter below situations:
If you need to compare the values of 2020 and last year at this time, but next time you need to compare the values of 2020 and 2018, you need to modify all measures that contain Year, or recreate these measures. When we have 100 measures, it will be a tedious work.
If you need to compare two periods that might have a different number of days, like comparing one month against a full year. Filter will be fixed in the formula and cannot be dynamically controlled by the slicer.
I will show you two ways to dynamically compare the value of two periods by slicer.