Forum Discussion

GijsBI's avatar
GijsBI
Icon for Helper II rankHelper II
1 year ago
Solved

Filtering categorical X-axis based on dynamic date

Hi everyone,   I have a dashboard where I compare model predictions to actual planned data over a series of dates. Both predictions and actuals overlap for a certain range of dates, but not necessa...
General Comment
Need Help
Tips and Tricks
  • jaineshp's avatar
    jaineshp
    1 year ago

    Hey GijsBI,

    I had to deal with the same issue once. The trick is to force your X-axis to only pick up the common weeks from both tables.

     

    • Make sure you’ve got a proper Week/Calendar table that links to Actuals and Predictions.
    • Create a small table of just the overlapping weeks:

    OverlapWeeks =
    INTERSECT (
    SELECTCOLUMNS ( Actuals, "Week", Actuals[Week] ),
    SELECTCOLUMNS ( Predictions, "Week", Predictions[Week] )
    ) 

     

    • Put OverlapWeeks[Week] on the X-axis instead of the week column from your fact tables.
    • Now when you switch Prediction runs, the axis adjusts and only shows the common range.

     

    That’s the simplest way I found to keep the chart clean and relevant.

     

    Fixed? ✓ Mark it • Share it • Help others!


    Best Regards,
    Jainesh Poojara | Power BI Developer

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi GijsBI 

 

You can handle this by creating a filter measure that only shows the overlapping weeks. The idea is to find the first and last week where both Actuals and Predictions have data, then only keep those.

Steps:

  1. Create measures for MinWeek and MaxWeek (weeks where both values exist).
  2. Create a flag like ShowWeek = IF(WeekNumber >= MinWeek && WeekNumber <= MaxWeek, 1, 0).
  3. Put ShowWeek = 1 as a visual filter on your chart.

Now your X-axis will only display the common weeks for Actuals and the selected Prediction run. If you have multiple years, build a YearWeek column to keep the order correct.