# Power BI Dynamic Visuals Using SWITCH TRUE Logic – Visualization Technique

One of the most common concerns I see in the Enterprise DNA Support Forum relates to creating dynamic visuals. In this blog, I’ll focus my discussion on multi-measure Power BI dynamic visuals. I have a tutorial about this topic, which I’d like to highlight in this article.

The tutorial will show you how to work and present your results in a dynamic way and how you can change the measure in which you want to show a comparison to. I set up some calculations that I then feed into a measure that will allow me to dynamically change with a selection.

Here are some of the forum posts from the Enterprise DNA Support Forum that go over this particular topic. Perhaps you have similar scenarios with these members, which you can definitely learn from.

## Multiple Financial Years For Different Companies

In this forum post, the member needed some filtering and merging of companies that have different year ends. One company has a February year end while the other has March. She was struggling with the Date table and time analysis in terms of filtering individual companies and consolidating the group.

This was how she did the filtering in her model.

1. Company 2 that has a February year end, and

2. Company 3 with a March year end.

So, there are two Financial Year (FY) dimensions. She wanted to create one dimension for FY, which would then give her a result of: if Company 2 is selected, February will be used as year end in measures and visuals. If Company 3 is selected, it will be March. Ultimately, she needed to consolidate the companies at a summary level.

The solution I gave her was to first create some measures to ultimately create what she required. So, instead of Total Sales, she should create Company 1 Sales and Company 2 Sales.

This is the formula I gave her:

CALCULATE( [Total Sales], Company = x )

After that, she’ll have to create a measure with the SWITCH/TRUE logic, wherein she’ll feed those measures inside it. This scenario has the same idea as the one discussed in the multi-measure dynamic visuals tutorial I mentioned at the beginning of this post.

In this case, the new measures created are then integrated into one measure with the SWITCH/TRUE logic.

Lastly, she needed to create a new table that has FY14, FY15, etc. Then, she’ll have to draw relationships from this table down to each column in her Date table with the unique financial years for each group.

This is how she can get the consolidation because she’ll be using the FY dimension in this table, which will filter the new measures concurrently based on the FY that she’ll select.

With that, she came up with this calculation that gave her the results she wanted.

## Variable Selection

This is another forum with a similar case. The member wanted to create a graph that would dynamically change from Revenue, Margin, and Cost of Sales based on the selection made in the corresponding filter.

Then, ultimately, he wanted to have a chart that compares Revenue and Budgeted Revenue when the “Revenue” is selected, but then changes to Margin vs Budgeted Margin when the “Margin” is selected.

Here’s what his report looked like:

So basically, it’s the same concept to use a combination of the SWITCH/TRUE logic and create a supporting table that has all the selection options to be used as the slicer.

## How To Filter With Date And Another Metric

Here’s another example of how you can create a dynamic visual with a filter slicer using the SWITCH/TRUE logic.

In this forum, the member was trying to make his P&L table dynamic to filter by date and have the value column change between Value or % of Revenue. He wanted to know how he could add a filter that allows to show either Value or % of Revenue.

The solution was for him to create a table with the metrics (Revenue and % of Revenue), then create a measure for a particular metric selected. This is how the formula should be:

Next, he has to create a measure to show the values if a particular metric is selected.

## Conclusion

Dynamic visuals using the SWITCH/TRUE logic is just one of the amazing visualization techniques that Power BI enables you to accomplish.

Creating dynamic visuals used to be very difficult and time-consuming with other analytical tools and programs. But with Power BI, you can do this very quickly.

However, it’s crucial that you understand and know your DAX well and build your data model well from the start. If you don’t know what functions to use or how to write the correct formula, you’ll be scratching your head for hours. If you have incorrect relationships and a too complicated model, you won’t be able to achieve the results you seek.

All the best!

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, you’ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations — the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...