Using DAX to make your Power BI DataViz more meaningful

One of the best features I like about Power BI Desktop is that the data acquisition, data modeling and data visualization functionalities are all integrated in a user friendly way. I don’t have to leave the Power BI desktop to perform any of these common operations when I am playing with my data, unlike so many other tools. Why is this important? Because you tend to be more productive when you have everything you need in one place and also, you tend to be more creative when you have the power to model the data along with making your visualizations. DAX has some really powerful data modeling capabilities, and when you couple that with Power BI, you can start giving more meaning to your visualizations and get faster insights. I recently made a video on my company’s blog site on how to analyze Promotion Effectiveness and for the same, I was using a dashboard made in Power BI Desktop. I am just highlighting two examples of how I used DAX to make my visualizations better.

I) Make better Sparklines by Highlighting

In my Promotion Effectiveness Analytics demo, the sparklines highlight (instead of just filtering) the value for the months where the selected promotions ran. This gives us a better understanding of what is happening before, during and after the promotion. Now, this is not possible out of the box in Power BI, but with just a little bit of DAX magic, we can make this work.


1) First let us see how to build a simple sparkline in Power BI. For that, select the month & sales and make it as a line chart.

1 Line Chart

2) Next, let us remove all the format options so that it looks like a sparkline. Also feel free to resize it

2 Power BI Sparkline

3) Make a new bar chart for Sales by Promotions, so that we can use it to filter the sparkline.

3 Bar chart for Promotions

You can see that the sparkline automatically gets cross-filtered to only the months where the promotion ran. This is great but what would add more value is if we could see the months highlighted instead of just filtered. That would let us know how the sales are before, during and after the promotions instead of just during the promotions.

4) Make a new measure by clicking on the dropdown next to the table, and then use the formula below

Sales Amount Total = CALCULATE([Sales Amount], ALL(Promotion))

4 Add new measure

Basically, we are making a measure which will show the Sales irrespective of whether the Promotions are filtered.

5) Now add the new measure to our sparkline (make sure to give it a lighter color like light grey for a better effect).

5 New Sparkline

Now you can see that the the grey line shows the sales for all the promotions while the green line highlights just for the selected promotion. You can also use this technique in other creative ways, for e.g., to highlight the max and min points of a sparkline.

6 Low High

II) Waterfall charts for Measures

In my Promotion Effectiveness Analytics demo, I created a Waterfall chart to show the breakdown of Customer Visits. Basically,

Customer Visits (This Year) = Customer Visits (Last Year) – Lost Customers + New Customers

I have individual measures for each of those, and in this case, a waterfall chart would be a great way to show the breakdown. However, we can only put columns in the category axis for waterfall chart in Power BI. But with some data modeling, we can get this done.


1) Make a dummy dimension called Customer Retention with just one column and 3 values – Last Year, New and Lost. I just made the dummy table in a text file and imported it to Power BI.


Note that this is a disconnected table and will have no relations to any other table.

2) Create a new measure called Customer Visits as shown below

Customer Visits = IF(HASONEVALUE(‘Customer Retention'[Customer Retention]),
                        SWITCH(VALUES(‘Customer Retention'[Customer Retention]),
                                “New”, [New Customers],
                                “Lost”, [Lost Customers],
                                “Last Year”, [Visits (LY)]))

Basically you are assigning the appropriate measures for New, Lost and Last Year based on the values for the disconnected Customer Retention table.


3) Now just make a Waterfall chart with the Customer Retention column and Customer Visits measure to clearly see the breakdown.

7 Waterfall Chart Power BI

Hope you got some ideas on what all we can do when we combine DAX with dataviz. Stay tuned for more as we expect to see Microsoft release more functionalities around the tool.

Note : Created using Power BI Desktop version listed below


Feel free to watch my video on analyzing promotion effectiveness by clicking on the image below

Demo Day: Analyzing the Effectiveness of Promotions in Retail

Posted by SQLJason


Minnaar Fourie

Great work SQLJason. When I look for stuff I mostly end up on your page for the solution. I am keen to know how to create in cell spark lines. I want to apply your solution for “Make better Sparklines for Highlighting” but in a single cell next to all line items – similar to the dashboards that the dashboard guru Stephen Few uses. Is this possible in Power BI:

Have you tried the Tadpole Spark Grid Plus in the PowerBI custom viz gallery – ? I think this should give more of an in-cell spark line (though you may have to play around a little bit, in case you have other text values and might need to superimpose another matrix next to or before the sparkline)

Tracy Nihda

Great column SQLJason!
I’m new to Power BI and DAX. Do you have any experience with changing the total value on a waterfall chart to a separate value? Ie start with forecast 1, add and subtract variables and then end with a separate forecast? It can be done in Excel but Power BI seems too rigid to allow that form of waterfall.

Can you reply with a screenshot to I did not understand the requirements here. As long as the Forecast2 and the sum/difference of individual components are the same, does it make a difference?

Hi Jason
Great work.
One thing I don’t get. For the waterfall, you create a table and 1 column (Customer Retention) that you use in “Category” for the graph. Then your Dax sentence assigns the right measure to each value of the Customer retention column. But what measure do you then pass into Y axis, given that you have 3 different measures (1 for each value of Customer retention column)?
Thx in advance


regarding: “You can also use this technique in other creative ways, for e.g., to highlight the max and min points of a sparkline.”

how can you generate markers on a line chart? Or do you use a custom visual here?

We created the Power BI Ultimate Waterfall Custom Visual.
It has a lot of clever functionality built-in. Like Chart orientation, small multiples, sub totals, Start value, deviation calculation and many more.
We are interested in your feedback!
Which functionality you still miss?

Venkattaramanan V

How to create a conditional color formatting in Line chart of power bi based on some rules ???

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.