- Ciao friends.
I want to show how to use the Power BI Tooltips
to display the filter context.
You know, we have here a chart
that can show information in the Tooltip in Power BI.
And you can customize this,
providing additional information through a measure.
However, for informational purposes,
I may want to include information about
what are the filters that are active here.
These filters could be the continents selected here,
in the slicer, or any filter we have
in the filter pane.
In a report, this information could be
not immediately visible.
So it could be useful to get a result like the one
that you see in this report
where when I select an element in this bar chart,
I see what is the filter applied to customer education,
to date year, year number, category and manufacturer.
And this is very important
'cause I could change the selection here.
For example I can select gray
and you see that now this changed
and I also see here that gray is a filter applied
to the color.
This could be useful for the end user
to know what is the filter applied to a chart,
but also for you who are developing a measure
to understand why the measure is not returning
the desired value.
Often times this is because there is some filter
that you forgot in the filter pane
that was applied to the visual.
But the question is, how can I obtain this?
Well, I need to create a measure.
If I select this visual, you see that in the Tooltips here,
we have a measure, Dump Filters.
And this measure, Dump Filters, just generate a string
that has all the information to display the Tooltip
that you have seen with all the information
about the filter context.
But this code is very hard.
How can I generate this code?
Well, the good news, I don't have to write this code.
DAX Studio can generate the code for me.
So if I open DAX Studio and I connect to my model,
I have access to all the tables and measures
and entities that I have in the model here.
So, what I want to do here is, I want to right-click
and I can say I can define a Filter Dump Measure
which means if I click on a table,
I create a measure that,
the definition of a measure,
that includes all the filters for all the columns
of that table only.
However, 'cause I want to create a visualization
in my Tooltip of any filter coming from any table,
I can use the other option.
So I can right-click on any table and I can choose this one.
Define Filter Dump Measure for all the tables.
Now, I will not spend time describing
how this code works
because as you see, there are 500 lines of code
and it would take too much time.
I just copy this code here and I go back here
and I create a measure.
So I want to do this operation here.
Remember, I didn't have here any additional information.
So I create a measure, right-click, new measure
and I paste my code.
The only thing I have to do,
so if I go at the beginning here, you see that
this DEFINE MEASURE was the code created in DAX Studio.
I have to remove this part and I just provide
after the assignment operator, after the equal to,
I have my code, the code generated by DAX Studio.
Before that, I provide the name... Info.
Let's call this just Info.
So I have,
because I already have a measure called Dump Filters.
Now, this measure returns the string
that includes all the information
about the filter context active in the current cell.
And when I go to my visual here,
you see that I move this info in the Tooltips.
Now, when I go here,
I see the information about the filters
coming from the filter context
of any column in any table of the model
included in the Tooltip in Power BI.
Enjoy DAX!