You have done your data modelling and your dataset is well structured and preferably in a star schema. The relationships are correct.
Then what?
This data is still raw data and it does not answer any questions the business is asking. What is the total revenue? What is the cost of production? You cant outright answer this questions.
This is where DAX comes in.
DAX- Data Analysis Expressions
This is a powerful formula language used in Power BI.
It is essential for creating calculations that unlock insights into your data. It is what says "
revenue this year is higher than last year" or " cost of production this year is up 4% as compared to last year".
DAX is important so as to optimize decision making in a business.
Calculated Columns and Measures in DAX
Calculated Column: This performs a row by row calculation in a table.
Measure: This returns a singular value for the context you choose(filter, aggregation)
DAX Functions
Text Functions
They manipulate texts.
- Concatenate/ConcatenateX
Concatenate joins two texts from different columns into one.
ConcatenateX is for each row. It returns the concatenation of these texts and is separated by a delimiter.
Left
Extracts characters from the left.Right
Extracts characters from the right.Mid
Extracts characters in the middle.Upper, Lower, Proper
Upper upper cases the whole text.
Lower lowercases the whole text.
Proper capitalizes only the first character.Len, Trim, Clean, Search
Len returns number of characters in a text.
Trim removes spaces from text.
Clean removes hidden non printable texts.
Search returns starting position of one text within another text.
Date and Time Functions
Today and Now
Today returns current date in datetime format.
Now returns current date and time in datetime format.Date, Day, Month, Year, Quarter
Date returns the specific date.
Day extracts the day of the month.
Month extracts the moth of the year.
Year extracts the year of a date.
Quarter extracts the quarter of the year in a number.
DateValue and TimeValue
DateValue converts a date in text to a date in datetime format.
TimeValue converts a time in text to a time in datetime format.DateDiff
This calculates the difference in specified intervals between two dates.
The interval can be in minutes, hours, weeks, months, years, quarter.
This gives the difference in years.
Aggregate Functions
Average, AverageX
Average calculates the mean of all numbers in a column.
AverageX is an iterator. It goes trough each row, takes the sum of these calculations and averages them.Sum and SumX
Sum adds all the numbers in a column.
SumX returns the sum of an expression evaluated for each row. For example, Sum of revenue when you have got yield and price. SumX will calculate revenue of each row then add the total.Max and Min
Max returns the largest value in a column.
Min returns the smallest value in a column.Count, Countrows, CountBlank, CountX, DistinctCount
Count returns the number of rows in a column that has no blanks.
Countrow returns the number of rows in a table.
CountBlank returns the total blanks in a column.
CountX returns the count of values which result from evaluating an expression for each row of a table.
DistinctCount returns the number of distinct values in a column.
Time Intelligence Functions
They are used to compare and aggregate data over periods of time. For example, revenue this month versus last month.
A data table should be present for the DAX functions to operate.
- DateAdd
It is used to move a date by a specified interval
If we are going forward, we use +ve.
If we are going backwards, we use -ve.
Here, we have shifted the date by two quarters back.
- TotalYTD, TotalMTD, TotalQTD, TotalWTD
They calculate cumulative totals.
They are Year-to-Date, Month-to-Date, Quarter-to-Date, Week-to-Date.
The same happens to the other functions.
- DateBetween
This returns the dates between two given dates.
Dashboard
After doing your calculations, you need to present this result to the decision maker. You do this by use of a dashboard.
Designing a good dashboard is crucial as it impacts how well the data you present will be interpreted.
It should be visually appealing and user friendly.
Dashboard Design
- Meaningful title. It should be concise and reflect the data you want to present.
- KPIs. These are Key Performance Indicators and they are the important numbers of a business, e.g., total revenue, profit margin.
- Charts. They show the performance visuals. They are placed in the middle section. Trend charts should start, followed by comparison charts then the breakdown visuals.
- Colors. Color palette should be limited to give a cleaner look. The colors should have high contrast. The background color should be simple and should not compete with the actual visualization.
- Layout. Avoid overcrowding the dashboard. Have a maximum of 6-8 visuals. You should not have any duplicate charts, e.g. area chart of monthly revenue and line chart.
- Interactivity. Have slicers, filters and drill down capabilities to enhance user interaction.
A good dashboard should turn raw data into actionable insights to support optimal decision making.
Top comments (0)