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

jaineshp's avatar
jaineshp
Icon for Memorable Member rankMemorable Member
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

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

    Hi Jainesh, 

     

    I think this is the appropriate solution. It does not work for me as expected, but that is because of my data model I think. I'll have to re-do the data model and then this would hopefully work. 

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

      Hi jaineshp , 

       

      Maybe you can help me with the last part of this issue. The logic you mentioned seems to work partly. The predictions table contains both run dates and prediction dates. Currently, the solution checks overlapping dates/weeks across all run dates and their corresponding predictions.

       

      However, what I need is to filter the predictions data so that it only shows prediction dates associated with the run date selected in the dashboard. I tried using SELECTEDVALUE and FILTER, but I haven’t been able to create a dynamic subset based on the slicer value.

       

      Do you know if there’s a way to achieve this?

       

      Thanks in advance for your help!