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 have data similar to this and currently have a measure which calculates distinct row count for each feature and until that date.
CumulativeRegistrations = CALCULATE(DISTINCTCOUNT(Previews[SubscriptionId]), FILTER (ALLEXCEPT(Previews, Previews[Feature]), Previews[Date] <= MAX(Previews[Date])))
I have an area chart with axis is Date , legend is feature and Values is the measure. and it's working perfectly.
But I have now added slicers for Date, customer and feature but the chart needs to honor these except Date in calculation.
Say I have selected Customer C1 and Feature F1 and Date slicer to start from 11/2. The value I want is 2 instead of 1, because there are 2 records in the table with C1 and F1 combination, as I changed formula to use ALLSELECTED which is skipping filtered out data.
How can I change formula to use filters for Feature and Customer but not Date. I still want the date slicer to be there so that chart shows the selected date range only.
Solved! Go to Solution.
Hi,
=CALCULATE(COUNTROWS(Data),DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))
Hope this helps.
Hi,
=CALCULATE(COUNTROWS(Data),DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))
Hope this helps.
it's working fine with Calendar table, but the date slicer shows the maximum date from Calendar.. is there any way I can restrict to Max date from the Data table so that it's easy for all kind of users in terms of scrolling the slicer .
Hi,
The Calendar table should be created using the New Table feature of PowerBI desktop (under modelling). Write this formula there
=CALENDAR(MIN(Previews[Date]),MAX(Previews[Date]))
perfect... worked fine..
Thank you.
I suggest you creat a calendar table and use the date column in that table instead of the previews table. That way when you can just remove the filter from the calendar table, and nothing else simple using ALL.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |