Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

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

by | 7:00 pm EDT | September 19, 2020 | Power BI

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

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Power BI Copilot: Enhancing Data Analysis with AI Integration

Are you ready to elevate your data analysis capabilities? Then let's delve into the realm of Power BI...

## What is Microsoft Fabric? A Guide to Features & Benefits

Imagine a one-stop shop that not only crunches numbers like it's eating cereal for breakfast but also...

## Python AttributeError: ‘dict’ object has no attribute Fix

Python is widely appreciated for its readability and ease of understanding. Among its numerous...

## Power BI vs Tableau: Differences Compared 2023

In the world of data visualization and analytics, two industry-leading data visualization tools stand...

## Pandas Drop Index Column: Explained With Examples

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality...

## Microsoft DP-500 Explained (Bonus Cheat Sheet)

Data is the backbone of businesses these days, and having proof that you know how to handle and make...

## Power BI Financial Dashboard Examples: Let’s Get Specific

Power BI financial dashboards provide a quick and easy way to monitor an organization's financial...

## Power BI Themes: User Guide With Examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various...

## Power BI Hierarchy: User Guide With Examples

Imagine being able to look at your data from every which way — from the bird's eye view right down to...

## Power BI Calendar Table: What Is it And How to Use It

Time is of the essence in the world of data analysis, and without a proper understanding of when or how...