Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
On the left side I have slicer for granularity - made with additional table connected with relationship in both directions.
On the right side I have slicer which limits the range of data.
How to combine those two solutions so that they would work?
https://drive.google.com/file/d/1XwIhdvOdNRbNfQNx9ws-EzrTXT_OkNy2/view?usp=sharing
One approach is to use a field parameter for the D/W/M/Q/Y slicer. Use the field parameter in the x-axis to enable dynamic selection of granularity.
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Proud to be a Super User!
The granularity works: D/W/M/Q/Y is a slicer based on the table:
hlp_Dynamic Date Selections =
UNION(
ADDCOLUMNS(
CALENDAR(
MIN(dimCalendar[Date]),
MAX(dimCalendar[Date])
),
"Visual Date", [Date],
"Type", "D",
"Order", 1
),
ADDCOLUMNS(
CALENDAR(
MIN(dimCalendar[Date]),
MAX(dimCalendar[Date])
),
"Visual Date",
[Date] - WEEKDAY([Date], 2) + 1,
"Type", "W",
"Order", 2
),
ADDCOLUMNS(
CALENDAR(
MIN(dimCalendar[Date]),
MAX(dimCalendar[Date])
),
"Visual Date", YEAR([Date]) & "-" & MONTH([Date]),
"Type", "M",
"Order", 3
),
ADDCOLUMNS(
CALENDAR(
MIN(dimCalendar[Date]),
MAX(dimCalendar[Date])
),
"Visual Date", YEAR([Date]) & "-Q" & CEILING(MONTH([Date])/3, 1),
"Type", "Q",
"Order", 4
),
ADDCOLUMNS(
CALENDAR(
MIN(dimCalendar[Date]),
MAX(dimCalendar[Date])
),
"Visual Date", YEAR([Date]),
"Type", "Y",
"Order", 5
)
)
Question is how to combine:
Date Param =
CALCULATE(
[Sales],
KEEPFILTERS(
DATESBETWEEN(
dimCalendar[Date],
[Selected Min Date],
[Max Date]
)
)
)
where
Selected Min Date =
VAR _MaxDate = [Max Date]
VAR _MinDate = [Min Date] - 1
RETURN
SWITCH(
'hlp_Date Selection'[Selected Date Selection],
"1W", _MaxDate - 7,
"1M", EDATE(_MaxDate, -1),
"3M", EDATE(_MaxDate, -3),
"TY", DATE(YEAR(_MaxDate), 1, 1),
"1Y", EDATE(_MaxDate, -12),
"ALL", _MinDate )
and
Max Date =
CALCULATE(
MAX(dimCalendar[Date]),
REMOVEFILTERS(dimCalendar[Date])
)
to automatically limit the range. If I choose 3M to limit the displayed data to three months?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |