Dynamically select measures to be shown on a Power BI visual via slicer

A few days ago a client asked me if it is possible to dynamically change series displayed on Power BI chart. My first (instinctive) answer was “Yes, of course, you can use a slicer to select which series you want to show, just put desired column in a slicer visual”. But then he added details: he wanted to select a measure to display on a chart, not to filter a value from column. My second (instinctive) answer was “No, you can’t. You can only filter a column, and can’t place measures in a slicer”.

But after a little chat I started to wonder whether it is really impossible. If we put a measure in a “Value” well of chart fields, it will be shown as a series (for example, some [Total Amount] measure). What my client is actually wants? He want to choose some elements on the slicer and, if one element selected, to show a measure. If that element is unchecked, then don’t show a measure.

Actually, those slicer’s elements are unique values from some column. A slicer applies a filter to that column. Can we catch whether a column is filtered? Yes, of course, we can do it with DAX. And if some desired value is selected, we just need to show a measure as a series. As that measure is already in the “Value”  well of a chart, then, in other words, we just have to “do nothing”. So, we only need to somehow hide a measure if a desired slicer’s element didn’t selected.

Continue Reading


What does it mean to be crossfiltered?

The history of “reinventing the bicycle” using DAX

Defining evaluation context and context transition rules is the most important and confusing part of DAX. Sometimes when you think you’ve already managed it, DAX turns to you with other side, hook, uppercut – and you’re knocked down.

Last week one of my Facebook friends asked me to explain why his measures working this way and not that way. It was quite easy questions and there is no sense to place them here. But my friend is a very curious man, one question led to another, and suddenly I found that I can’t explain a very simple, on the first look, concept. The question was about filtering under context transition in calculated column (here you can imagine a very big grin on DAX’s face).

For this post I reworked and simplified data model.

There are two tables, named Managers  and Sales . Managers  has only two columns: [Manager]  and [Department] .

'Managers' table

‘Managers’ table

‘Sales’ has a little bit more columns (although it doesn’t matter here): [Order] , [Manager] , [Amount]  and [Order type] .

'Sales' table

‘Sales’ table

As you can see, they are linked one-to-many by [Manager]  column. Quite easy.

Data Model

Data Model

First of all my friend asked me, how context transition works. “Hey, it’s easy!” – I said, and Continue Reading