Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
FRANKADVIES
Frequent Visitor

How to let the tooltip page filter properly with the date values on the X-axis of a visual?

I have created a line chart that shows for a specific metric the development of its cumulative values (y-axis) for 12 provinces (legend) during the selected weeks (x-axis). The selected weeks on the x-axis can be chosen with a date slicer on the same page.

 

I've created a tooltip page, because the regular tooltip can only show 10 provinces. When you hoover over a specific week in the visual, the tooltip has to show the same values as the lines in the graph. For each week that should be the cumulative value of each province since the first selected week (actually the first date) of the date range that has been selected with the date slicer as shown on the x-axis. Therefore I use the same metric on the tooltip page as I use in the visual. But that doesn't seem to work.

 

In the visual I use the following metric to draw the lines (while using Province as legend): Cumulative value since first date in selected date range = CALCULATE ( [Specific metric] , FILTER ( ALLSELECTED ( Dates[Date] ) , Dates[Date] <= MAX ( Dates[Date] ) ) ).

 

But, when I use that same metric for the tooltip page, it calculates different values for each week. It calculates for each week the cumulative value since the start of that week, and not since the first date of the selected date range (as selected with the date slicer). So, for example, if I select a date range starting on the first date of week 20 until the last date of week 23 with the date slicer, the cumulative values of a Province in the graph are 10 for week 20, 15 for week 21, 45 for week 22, and 60 for week 23. But the tooltip calculates the cumulative vale since the start of each week, so the values of that province are, 10 when I hoover over week 20, 5 when hoovering over week 21, 30 when hoovering over week 22, and 15 when hoovering over week 23.....Is this because using  ALLSELECTED on the tooltip page regards the dimensions on the X-axis of the graph as explicit filters too?

 

Does anyone know how to solve this? Thanx in advance!

 

2 REPLIES 2
FRANKADVIES
Frequent Visitor

Thanks Greg, I've chekced the other issues before but couldn't find a solution. Below I've attached some info to be more specific (should have done that earlier;-)

1) A sample dataset with the daily cases of two provinces in three weeks

ProvinceDateWeekCases
Utrecht5-jul-20285
Utrecht6-jul-20286
Utrecht7-jul-20283
Utrecht8-jul-20287
Utrecht9-jul-20288
Utrecht10-jul-20283
Utrecht11-jul-20283
Utrecht12-jul-202910
Utrecht13-jul-202912
Utrecht14-jul-20298
Utrecht15-jul-20296
Utrecht16-jul-20297
Utrecht17-jul-202911
Utrecht18-jul-202910
Utrecht19-jul-20309
Utrecht20-jul-203014
Utrecht21-jul-203012
Utrecht22-jul-203010
Utrecht23-jul-203016
Utrecht24-jul-20308
Utrecht25-jul-203017
Zeeland5-jul-202820
Zeeland6-jul-202818
Zeeland7-jul-202825
Zeeland8-jul-202820
Zeeland9-jul-202818
Zeeland10-jul-202819
Zeeland11-jul-202815
Zeeland12-jul-202918
Zeeland13-jul-202912
Zeeland14-jul-202910
Zeeland15-jul-202914
Zeeland16-jul-202912
Zeeland17-jul-202916
Zeeland18-jul-202910
Zeeland19-jul-20308
Zeeland20-jul-203011
Zeeland21-jul-20309
Zeeland22-jul-20307
Zeeland23-jul-20306
Zeeland24-jul-20309
Zeeland25-jul-20305

2) An example of the the visual I generate with these data in my report

demo.jpg

 

 

 

 

 

 

 

The visual uses the measure 'Cumulative cases' as value and the field Province as legend. The DAX formula of this measure= CALCULATE ( SUM ( Cases[Cases] ) , FILTER ( ALLSELECTED ( Dates[Date] ) , Dates[Date] <= MAX ( Dates[Date] ) ) )

3) Goal: a tooltip page with a table with all 12 provinces in the first column and the cumulative number of cases in the selected week in the second column.  In my report this graph has 12 lines (all 12 provinces in The Netherlands). Therefore this visual has no labels in my report as in the present visual above, because that makes the visual unreadable. Instead, I want to use to use the tooltip for that purpose. However, the default tooltip can show max 10 values. That's why I have made a tooltip page to show for each week the values of all 12 provinces. 

4) Issue: I don't know which DAX formula to use to create the second column in the table on my tooltip page. At first, I used the same measure as in the visual. But when I use that on the tooltip page. That formula calculates the cumulative number of cases in each week, and not the cumulative number of cases since the first date of the selected date range with the date slicer. In the case of the example visual (2) it will calculate for province Utrecht 135 in week 28, 64 in week 29 and 86 for week 30.

I think that's because I use ALLSELECTED in the DAX formula, because on the tooltip page this DAX formula will therefore look for the selected date range in the date slicer and for the selected week in the visual as well.  

5) My question is which DAX formula to use on the tooltip page to calculate the cumulative number of cases in each week since the first date of the date range that has been or will be selected with the date slicer? ...I've tried to use ALL or ALLEXCEPT in the DAX formula to replace the ALLSELECTED, but that hasn't worked for me yet... 

Frank 

 

 

Greg_Deckler
Super User
Super User

@FRANKADVIES - If I am following this correctly, yes, the problem is almost certainly the ALLSELECTED having a different context in the report versus the tooltip page. The page is passing the week on to the tooltip page as a context filter. You will need to use ALL or ALLEXCEPT. Can't be more specific without more information.

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.