Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to get the Date Range - Min and Max Dates of a Between Date slicer.
Specifically the MIN Date which I need to calculate the average only for the selected period.
However when I click on any column in the chart I can't get the correct MIN Date!
Here's a sample file
Any help/ideas will be greatly apprecaited!
Thanks!
@OwenAuger@Phil_Seamark@KHorseman
That date will actually be used on a Tooltip Page and the chart you see on the right is actually on the "visible" page.
But what happens when you hoover over the columns of the chart each column in effect filters the results of the tooltip page just like if you've clicked on the column in the sample file! This is fine for everything I display on the tooltip page except the average I'm trying to calculate which calculates only for the specific column/date or for the entire calendar. Hope this makes sense!
Solved! Go to Solution.
Hi @Sean
In this case, I think the issue is that when you click on a bar of the column chart (or hover in the case of a tooltip), any other visuals that are filtered by this visual are filtered just the same as if a slicer had been filtered on the Date corresponding to that bar. In the words of this article, the last shadow filter context of 'Calendar Table'[Date] becomes the same as the Date filter context itself, so ALLSELECTED has no way of retrieving the Date filter from the slicer.
To achieve your intended result, I believe you need two separate Date columns: one for the slicer and one for the chart.
You could either use the Date from the fact table as your second date, or construct another Date table related to the first.
I've gone with constructing another Date table, sort of a similar approach to Alberto Ferrari in this article.
Min Date Range ALLSELECTED =(actually Min Date Range ALL as already defined also works with these model changes)
CALCULATE (
MIN ( 'Calendar Table Filter'[Date] ),
ALLSELECTED ( 'Calendar Table Filter')
)
Also related idea in this article
http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...
Regards,
Owen
Hi @Sean
In this case, I think the issue is that when you click on a bar of the column chart (or hover in the case of a tooltip), any other visuals that are filtered by this visual are filtered just the same as if a slicer had been filtered on the Date corresponding to that bar. In the words of this article, the last shadow filter context of 'Calendar Table'[Date] becomes the same as the Date filter context itself, so ALLSELECTED has no way of retrieving the Date filter from the slicer.
To achieve your intended result, I believe you need two separate Date columns: one for the slicer and one for the chart.
You could either use the Date from the fact table as your second date, or construct another Date table related to the first.
I've gone with constructing another Date table, sort of a similar approach to Alberto Ferrari in this article.
Min Date Range ALLSELECTED =(actually Min Date Range ALL as already defined also works with these model changes)
CALCULATE (
MIN ( 'Calendar Table Filter'[Date] ),
ALLSELECTED ( 'Calendar Table Filter')
)
Also related idea in this article
http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...
Regards,
Owen
I ended up using the Calendar Filter table but because each Product had a different starting date I had to add a simple IF statement to decide which date to use when calculating the average for the selected product.
Even though we got the correct Min Slicer Date when a column was clicked I had to add this simple condition...
If the Min Slicer date selected was prior to the Min Overall Date for the selected product use the Min Overall Date
otherwise go with the Min Slicer Date to calculate the average.
Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |