How create a dynamic Tooltip in Power BI using DAX
Hello guys, a lot has passed without writing something and that is why this time I want to share a technique on how to create a dynamic tooltip in Power BI using DAX language program.
Based on my experience developing in Power BI I, have come across a requirement about dynamic Tooltips, which is what the end user needs. Power BI gives us the possibility to create a custom Tootip in a new tab, But what happens when what the user needs is something that does not conform to what the different visualizations offer him and neither does the structure of the data help, since it is intended to reflect as a tooltip measures calculated with the occasional business rule that is must apply or a specific filter. That complicates our development. Creating dynamic tooltip is possible in power bi, on the internet there are a few videos of some good references in the use of this tool, which helped me to devise how I could solve this development and be able to fulfill my deliverable.
The example that I will show is about generating a tooltip that describes a comment for each column that contains a calculation in a Power BI grid.
It doesn’t look complex when you look at the table and see the comments that are the explanation of each calculation. The complex thing is when the measures for that grid come from different tables and also have one or another business rule or exclusion of values. Like this.
The purpose of creating a data model is to show the consistency of the same data at the time of visualization. In practice, this is difficult to carry out when there are requirements that are not compatible, either with the structure of the data or with the capabilities of the visualization tools. It is then that the developer uses his ingenuity and / or experience to be able to give the user what he wants without losing the integrity of the data and the coherence of the information, in case he has had to make last minute changes.
Sorry for extending the explanation, but it never hurts to make some clarifications in order to be more clear about what is intended to be done and what the scenario or use case is. Let’s get started !!!
The first thing we will do is load the excel data. You can do it in the way that best suits you, I preferred to copy and paste the selection.
Then we will add a field with the name of INDEX and we will set the value to 1, giving it text format, for all records. In the Item field there are those 2 points at the end of the text of each record, I delete them in the Power Query editor.
Our table is already loaded in Power BI but it has no relation to my data model. The way we can give you the relationship is through the generation of a calculated field that contains the same INDEX field and also the value of 1 to be able to relate it, but that is a later step for this case, since otherwise you would have to add that field to all the tables where the columns that contain the values of the measurements of the grid are located, and that would break my model as it is and generate calculation errors, in addition to how it could be raised in DAX … that’s what hard.
We will create a SUMMARIZE table with the fields and measures that make up the grid. Having already created our table, can you find the similarities and differences?
First of all: "you should know that you can convert a measure into the disaggregated value in a table of your model, for this you can use the SUMMARIZE and depending on the context, you can accompany it with the FILTER statement, that is useful when you need to your calculated measurement is part of an axis in some visualization".
Mesures
Ventas = SUM(FactInternetSales[SalesAmount])
Reventa = SUM(FactResellerSales[SalesAmount])
PPM = SUM(FactInternetSales[ProductStandardCost])
AVG_Costo = AVERAGE(FactProductInventory[UnitCost])
Entradas = SUM(FactProductInventory[UnitsIn])
Creating a summarize table with DAX
Tabla =
SUMMARIZE(DimProduct,DimProduct[ProductLine],
“Ventas”,[Ventas],
“Reventa”,[Reventa],
“PPM”,[PPM],
“AVG Ganancia”,[AVG_Ganancia_%],
“Entradas”,[Entradas])
Although we are consuming the calculated measures as fields for our table and the results are very similar, that is wrong, because our created table is not related to our model, so it will not respect the filters that are contemplated in the example grid. We must consider that the filters that the grid has are at the object level, and to correct our summarize table, we must refer to those filters into the measures with DAX language. Therefore, our Table will now have the following code:
Tabla =
FILTER(
SUMMARIZE(DimProduct,DimProduct[ProductLine],
“Ventas”,CALCULATE([Ventas],
FILTER(DimProduct,DimProduct[ProductLine] <> BLANK()),
FILTER(DimProduct,DimProduct[Status] = “Current”)),
“Reventa”,CALCULATE([Reventa],
FILTER(DimProduct,DimProduct[ProductLine] <> BLANK()),
FILTER(DimProduct,DimProduct[Status] = “Current”)),
“PPM”,CALCULATE([PPM],
FILTER(DimProduct,DimProduct[ProductLine] <> BLANK()),
FILTER(DimProduct,DimProduct[Status] = “Current”)),
“AVG Ganancia”,CALCULATE([AVG_Ganancia_%],
FILTER(DimProduct,DimProduct[ProductLine] <> BLANK()),
FILTER(DimProduct,DimProduct[Status] = “Current”)),
“Entradas”,CALCULATE([Entradas],
FILTER(DimProduct,DimProduct[Status] = “Current”),
FILTER(DimProduct,DimProduct[ProductLine] <> BLANK())),
),[Ventas] <> 0
)
As you can see in the image, now the result is the same at the record level and at the total level, with the exception, of course, that for the “AVG Ganancia” field, we must put a percentage format and show the average, this We do it by going to the menu where the aggregations are displayed.
Ah.. and the other is the order of the product line, but that’s a minor detail. However, we still haven’t gotten our new table to be related to our model, so it doesn’t do us much good as it is. What we will do is add a field with the name “INDEX” and we will set it with the value “1” within the measure of the DAX code with which we create the table, for which, we will have.
In the table that we have loaded with the explanations, in the Power Query environment, we will add a column with the same name “INDEX” and we will set the “1” as a value, with the same data type that we gave to the table that is I believe previously, and with that we will have the following (I remove the points from the item and the tick from the title of the field)
Please don’t fall asleep, there is little left. We need to create one more table that contains the relationship of the explanations with the values of the grids, for this, we will make a Join using the expression NATURALLEFTOUTERJOIN, and we will do it by invoking the fields of the calculated table that we create and we will encapsulate it in variables, notice how :
Tabla2 =
VAR
DATOS1 =
SELECTCOLUMNS(
‘Tabla’,
“ProductLine”, ‘Tabla’[ProductLine] & “”,
“Ventas”,’Tabla’[Ventas],
“Reventa”,’Tabla’[Reventa],
“PPM”,’Tabla’[PPM],
“AVG Ganancia”,’Tabla’[AVG Ganancia],
“Entradas”,’Tabla’[Entradas],
“Indice”,’Tabla’[Indice] & “”
)
VAR
DATOS2 =
SELECTCOLUMNS(
‘Tabla1’,
“Item”, ‘Tabla1’[Item] & “”,
“Explicacion”,’Tabla1'[Explicacion],
“Indice”, ‘Tabla1’[Indice] & “”
)
RETURN
NATURALLEFTOUTERJOIN(DATOS1, DATOS2)
We will obtain a table with all duplicate values, according to the number of fields with which I made the JOIN. This was the purpose in the beginning, that is why the key to relate was a unique one for all the records, so when we do the LEFTJOIN we bring the lines from the other table, according to their order and that number of records will be repeated by each record in the other table, but it will give us what we need, which are the “Item” and “Explanation” fields as we want them. Now we can create a relationship between Tabla1 and Tabla2 by Item.
Once our tables are related, we proceed to create our tooltip as requested (in the example case, this logic is used for many more, especially for groupings by filters, you’ll see)
What follows now is to create a calculated measure that contains the values of all the fields of the last table that we created, only the fields that contain the aggregation values.
Valor =
SWITCH(
SELECTEDVALUE(
‘Tabla2’[Item]),
“Ventas”,
SUM(Tabla2[Ventas]),
“Reventa”,
SUM(Tabla2[Reventa]),
“PPM”,
SUM(Tabla2[PPM]),
“AVG Ganancia”,
FORMAT(AVERAGE(Tabla2[AVG Ganancia]),”00.00%”),
“Entradas”,
SUM(Tabla2[Entradas])
)
Now in order to have a grid equal to the one they asked us in our example, with the correct calculations and that allows us to have the dynamic tooltip, we will use a matrix since the measure we create contains all the values that will be distributed according to the name of the field . Look at the image that the values are as they asked us (the format must be adjusted according to your need) a minor detail is the order that is not for now is not the objective.
The last step is to call the Tooltip that we have created, and now is when the magic happens.
In this way we could make the tooltip dynamic without making mistakes and be able to relate it to my model through the field by which I am grouping in the grid. There may be another way and perhaps it is better and simpler, but it is the one I found and it helped me a lot in my report development, since also by adjusting the calculations a little more in DAX we could create a dynamic grouping of the fields of my grid.
Valor2 =
SWITCH(SELECTEDVALUE(FILTRO[Medidas]),
“Primarias”,
SWITCH(
SELECTEDVALUE(
‘Tabla2’[Item]),
“Ventas”,
SUM(Tabla2[Ventas]),
“Reventa”,
SUM(Tabla2[Reventa])),
“Secundarias”,
SWITCH(
SELECTEDVALUE(
Tabla2’[Item]),
“PPM”,
SUM(Tabla2[PPM]),
“AVG Ganancia”,
FORMAT(AVERAGE(Tabla2[AVG Ganancia]),”00.00%”),
“Entradas”,
SUM(Tabla2[Entradas])))
I hope you find it useful. Take good care of yourselves and let it be Until next time !!!
