Using DAX In Data Models In Power BI


Using DAX in data models in Power BI

So ways in this chapter, we have presented numerous examples of visualizations. We’ve established how you can improve your reports via deciding on the proper visualization, properly placing the properties, and even installing custom visualizations whilst required. However, there are some of the upgrades that you could achieve that do not require a right away action at the visualizations. You can as an alternative create measures or calculated columns in DAX. Usually, you operate DAX to reap a certain numeric result, but sometimes you may take advantage of a DAX expression to govern the report layout.

Our first example concerns the measures used within the candlestick charts of the report proven in Figure 7-28. Every time period displayed involves 4 measures: Open, Close, High, and Low. The statistics recorded in the information model has four corresponding columns for each day and stock. However, you would possibly use the candlestick chart to display facts through a week or by way of the month in preference to by way of the day. Also, follow us Power BI Online Training 

The aggregation required for every measure relies upon the degree itself. The Open measure’s rate needs to be the DayOpen cost of the primary day inside the period, the Close charge should be the DayClose cost of the ultimate day inside the period, the High price should be the maximum value of DayHigh within the period, and the Low fee ought to be the minimum cost of DayLow within the period.
You can write these four measures by the usage of the subsequent DAX expressions:

Open = IF ( HASONEVALUE( StocksPrices[Date] ), VALUES ( StocksPrices[DayOpen] ), CALCULATE ( VALUES ( StocksPrices[DayOpen] ), FIRST DATE ( StocksPrices[Date] ) ) )
Close = IF ( HASONEVALUE( StocksPrices[Date] ), VALUES ( StocksPrices[DayClose] ), CALCULATE ( VALUES ( StocksPrices[DayClose] ), LAST DATE ( StocksPrices[Date] ) ) )
High = MAX ( StocksPrices[DayHigh] )
Low = MIN ( StocksPrices[DayLow] )

As referred to in advance in this chapter, another beneficial tip is to create a measure simply to show a measure with a distinct name. The reason is that many visualization additives use the measure call in a legend or different description, and also you do not have a manner to rename that by way of the usage of visualization houses. For instance, when you have measured, Current and Previous, however, you need to show the exact year in a selected visualization, you may create measures with the exact year that you want to show in the legend, as inside the following example:

[2014] = [Previous]
[2015] = [Current]

In a document that you may see within the next section, we can use information extracted from Google Analytics. The Website desk consists of the columns Users and Country ISO Code.

In the dashboard, it will likely be interesting to show on a map the ratio between the range of customers touring a website and the populace of the country/vicinity where users come from, but such statistics aren't always to be had directly from Google Analytics. You can import the facts approximately countries’/regions’ populations in a separate Countries/Regions desk and hyperlink it to the Website table the use of the Country ISO Code column

Because the ratio could be a decimal range, you can create a metric referred to as Users Per Million by way of the use of the subsequent measure definition in DAX:

Users consistent with Milli (
SUM ( 'Website'[Users] ),
SUM ( 'Countries'[Population] )
) * 1000000

You should not assume that a visualization component might at once do a calculation, which includes a ratio of a difference. It is always better to outline the corresponding calculation in a DAX You can split the problem into steps. First, you extract the width length from the string, changing the digits before the “x” character in an integer number.

Then, you evaluate this wide variety with a listing of predefined values representing the interesting range of resolutions you want to analyze, which includes 1024, 1280, 1440, 1920, and 2560. You can see the two calculations implemented inside the following two measures, Width Size and Width Category, respectively measure, and then you bind that measure to the visualization. Learn more Power BI Certification

Finally, you ought to keep in mind the usage of a calculated column whilst you want a type that agencies existing facts with an excessive granularity to a lower number of unique values which are less difficult to show in a chart. For example, Figure 7-30 shows the present values within the Browser Size column this is furnished by means of Google Analytics. There are more than 5,000 particular combos of width and height, and this fragmentation of values makes the analysis harder. Moreover, each resolution is a single string, and the reviews should group one-of-a-kind resolutions with the aid of width, ignoring the height.

Width Size =
VAR xPos = FIND ( "x", Website[Browser Size], , 0 )
VAR widthString = IF ( xPos > 1, LEFT ( Website[Browser Size] , xPos - 1 ), "" )
RETURN IF ( widthString "", INT ( widthString ) )
Width Category =
TRUE(), Website[Width Size] <= 1024, 1024,
Website[Width Size] <= 1280, 1280,
Website[Width Size] <= 1440, 1440,
Website[Width Size] <= 1920, 1920,
Website[Width Size] <= 2560, 2560,
CALCULATE ( MAX ( Website[Width Size] ), ALL ( Website ) )


Keywords: power bi online training,power bi certification,power bi course,power bi training


Other related blogs

Common mistakes that should be avoided while doing Law Assignments

By : CrazyForStudy

We have often overheard our professors complaining about the poor quality of assignments. Let's ad..

Common mistakes that should be avoided while doing Law Assignments

By : CrazyForStudy

We have often overheard our professors complaining about the poor quality of assignments. Let's ad..

Emergency Funds

By : Freddie “This is what you chose to do,” they..

Tips For Writing Good Essay

By : WriteAnEssayForMe

Don’t you wish there was an online writing service that would complete your assignments for yo..