Excluding Weekly Total Less than specific value From Dashboard

Hi cheid_4838 ,

 

To exclude weeks where a driver has less than 1,000 miles across the entire Power BI dashboard, you can create a DAX measure that calculates the total weekly miles per driver and returns 1 if the value meets or exceeds 1,000, otherwise 0. This measure can then be used as a filter across the report.

Drv1K_Filter =
VAR WeeklyMiles =
    CALCULATE(
        SUM(DOMTAR[Total Miles]),
        ALLEXCEPT(DOMTAR, DOMTAR[Driver Name], DOMTAR[Week Start Date])
    )
RETURN
    IF(WeeklyMiles >= 1000, 1, 0)

Once the measure is created, apply it as a filter in the Filters pane at the page or report level by setting it to 1. This ensures that only drivers who have at least 1,000 miles in a given week remain visible in all visuals. If certain visuals do not accept the filter directly, you may need to modify individual measures to incorporate the filter condition within CALCULATE to ensure the correct values are displayed. An example of an adjusted measure could be:

Total Miles Adjusted =
CALCULATE(
    SUM(DOMTAR[Total Miles]),
    FILTER(
        ALL(DOMTAR),
        [Drv1K_Filter] = 1
    )
)

This approach will dynamically remove weeks where a driver’s total miles fall below 1,000, ensuring that the yellow-highlighted values in your data are excluded from all measures and dashboard visuals. By applying the filter at the report level, you avoid the need to modify every measure individually, simplifying maintenance while ensuring consistency across all calculations.

 

Best regards,

cheid_4838 

Create a measure to calculate weekly miles for each driver:
DAX
WeeklyMiles =
CALCULATE(
SUM(DOMTAR[Total Miles]),
ALLEXCEPT(DOMTAR, DOMTAR[Driver Name], CalendarLookUp[Week Start Date])
)

Create a measure to flag drivers with less than 1,000 weekly miles:
DAX
Drv1KFlag =
IF(
[WeeklyMiles] < 1000,
1,
0
)

Create a measure to use in your visualizations that excludes drivers with less than 1,000 weekly miles:
DAX
FilteredMiles =
CALCULATE(
SUM(DOMTAR[Total Miles]),
FILTER(
DOMTAR,
[Drv1KFlag] = 0
)
)
Apply the FilteredMiles measure to your visualizations:
Replace the existing measures in your visualizations with the FilteredMiles measure. This will ensure that only drivers with 1,000 or more weekly miles are included in the calculations.

Set up a visual-level filter:
Alternatively, you can set up a visual-level filter to exclude drivers with less than 1,000 weekly miles. To do this, add the Drv1KFlag measure to the filter pane of your visualizations and set the filter to only include values where Drv1KFlag is 0.