Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
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
Province | Date | Week | Cases |
Utrecht | 5-jul-20 | 28 | 5 |
Utrecht | 6-jul-20 | 28 | 6 |
Utrecht | 7-jul-20 | 28 | 3 |
Utrecht | 8-jul-20 | 28 | 7 |
Utrecht | 9-jul-20 | 28 | 8 |
Utrecht | 10-jul-20 | 28 | 3 |
Utrecht | 11-jul-20 | 28 | 3 |
Utrecht | 12-jul-20 | 29 | 10 |
Utrecht | 13-jul-20 | 29 | 12 |
Utrecht | 14-jul-20 | 29 | 8 |
Utrecht | 15-jul-20 | 29 | 6 |
Utrecht | 16-jul-20 | 29 | 7 |
Utrecht | 17-jul-20 | 29 | 11 |
Utrecht | 18-jul-20 | 29 | 10 |
Utrecht | 19-jul-20 | 30 | 9 |
Utrecht | 20-jul-20 | 30 | 14 |
Utrecht | 21-jul-20 | 30 | 12 |
Utrecht | 22-jul-20 | 30 | 10 |
Utrecht | 23-jul-20 | 30 | 16 |
Utrecht | 24-jul-20 | 30 | 8 |
Utrecht | 25-jul-20 | 30 | 17 |
Zeeland | 5-jul-20 | 28 | 20 |
Zeeland | 6-jul-20 | 28 | 18 |
Zeeland | 7-jul-20 | 28 | 25 |
Zeeland | 8-jul-20 | 28 | 20 |
Zeeland | 9-jul-20 | 28 | 18 |
Zeeland | 10-jul-20 | 28 | 19 |
Zeeland | 11-jul-20 | 28 | 15 |
Zeeland | 12-jul-20 | 29 | 18 |
Zeeland | 13-jul-20 | 29 | 12 |
Zeeland | 14-jul-20 | 29 | 10 |
Zeeland | 15-jul-20 | 29 | 14 |
Zeeland | 16-jul-20 | 29 | 12 |
Zeeland | 17-jul-20 | 29 | 16 |
Zeeland | 18-jul-20 | 29 | 10 |
Zeeland | 19-jul-20 | 30 | 8 |
Zeeland | 20-jul-20 | 30 | 11 |
Zeeland | 21-jul-20 | 30 | 9 |
Zeeland | 22-jul-20 | 30 | 7 |
Zeeland | 23-jul-20 | 30 | 6 |
Zeeland | 24-jul-20 | 30 | 9 |
Zeeland | 25-jul-20 | 30 | 5 |
2) An example of the the visual I generate with these data in my report
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
@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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
19 | |
17 | |
12 | |
9 | |
9 |