DAX is much more then creating calculated columns and calculated measures. As the name suggests, Data Analysis Expression (DAX) is the language used to express data. DAX can be used in Power BI for data transformation as well as data visualization. In this blog post, the use of DAX to enhance the visualization capacities of a simple bar graph are discussed. The data model used for this blog consists of two tables: Sales_Table and Product_Table. The Product_Table is a dimension table containing data related to the products and the Sales_Table is the fact table containing transactional data. In the first half of this blog, calculated measures are created to bridge the gap for the missing data. These measures are used in the second half to create dynamic bar graph and show different values on the same bar graph based on the user selection.
To Get Started:
- Create a table All Measures for measure values.
Create a measure to calculate Revenue
- Create a new measure Revenue according to the following DAX expression.
Create a measure to calculate the cost of goods sold
3. Create a new measure COGS according to the following DAX expression
Create a measure to calculate Profit
4. Create a new measure Profit according to the following DAX expression.
This is how our data model looks like after we created the measured required. Now let’s Perform Conditional Formatting of a bar chart using DAX.
First we create a table to get selection from user:
1.Click on Home Tab in the Ribbon Menu.
2.Click on Enter Data in the External Data
3.Name Column1 to Selection.
Enter the Column/Measure names that will be used as dynamic switching options.
4.Enter the Value COGS in the cell.
5.Enter the value Profit in the cell.
6.Enter the Value Revenue in the cell.
7.Click Load.
Switch DAX function can also be used to perform conditional formatting. It allows you Switch between Different Values based on your selection.
Now create a new measure using the Switch function
8.Click on New Measure.
9.Type the Following DAX expression in the formula bar.
Switch allows user to choose between different options and returns the value according to the input selected.
Create a slicer to provide slicing options
10.Now Click on Slicer Visual in the Visualization pane.
11.Add the Selection column from Table to Values.
Finally, create a bar chart whose axis can be dynamically changed.
12.Click on Bar Graph in the Visualization pane.
13.Drag the Product from Product_Table to the Axis.
14.Drag the Measure to the Values.
Click on values in the slicer selection to conditionally format the axis of bar chart.
Conclusion:
DAX is much more then creating calculated columns and calculated measures. DAX acts like a cherry on the cake by allowing you to create dynamic bar graph. This blog post describes one of the ways in which DAX can be used to enhance the capacities of a bar graph visual.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
