Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter date table based on start and end date from another table's selected rows

Hi everyone. I've spent the last couple of hours trying multiple arpproaches to this simple problem, but can't seem to work it out. I will highlight what it is I need and mention the things I've tried at the end

 

Here is the .pbix file and data to replicate my problem.

 

What I need:

In the following dashboard, I want the line plot on the right to ONLY show data for the date ranges selected in the table on the left. In other words, the [Date] column of the dji table should ONLY contain values between the [Start Service] and [End of Service] columns of the selected/highlighted rows of the table.

Figure 1

 

If multiple rows are selected with a non-continuous time period, I am okay with a solution that either:

  1. Filters the data between MIN([Start Service]) and MAX([End of Service]) of all selected rows (Figure 2) OR
  2. Filters data between each of those ranges in a non-continuous manner

 

What I've done so far (and failed):

  • Attempted to create an intermediate date column (as follows) and establish a relationship with the DJI table

 

 

 

dates = ADDCOLUMNS(
    CALENDAR(MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]),MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
    "Year",YEAR([Date]),
    "Month",MONTH([Date]),
    "Day",DAY([Date]),
    "DayOfWeek",WEEKDAY([Date],2)
)

 

 

 

  •  Created an Include measure in the DJI dataset

 

 

 

Include = 
    IF(
        (dji[Date] >= MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]) && dji[Date] <= MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
        "Include",
        "Exclude"
    )

 

 

 

 In each of these cases, the resulting Table/Measure just did not update based on the selected rows.

Need Help
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago

    So far, I'm able to project selected terms from pres_congres, whether contiguous or uncontiguous, to dji; but the performance of subsequent average calculation is terribly poor ... I'll leave it to you or others until I get some inspiration.

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    3 years ago

    So far, I'm able to project selected terms from pres_congres, whether contiguous or uncontiguous, to dji; but the performance of subsequent average calculation is terribly poor ... I'll leave it to you or others until I get some inspiration.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This is it sir. Performance on my computer is not an issue at all. I will probably find a way to optimize this in the future, but this is the solution I was looking for. Thank you so very much.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak. I went through your videos and I'm afraid I couldn't figure out how the resources will get me closer to what I want. Any chance you could use the PBIX and data files and share the solution?

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    3 years ago

    There's a slightly easier way to this sort of thing. What you could do is write two seperate measures, BeginDate and EndDate. BeginDate will be:

    BeginDate =
    VAR V_Start =
    CALCULATE(
        MIN([Start Service]
        )
    )

    VAR V_End =
    CALCULATE(
        MAX(
            [End Service]
        )
    )

    RETURN
    IF(
        V_Start <= V_End,
        V_Start,
        V_End
    )
     
    And end date would be the inverse of this. Then, in your actual calculation you can do this:
    CALCULATE(
        [Your Thing To Calculate],
        FILTER(
            [Date Table],
            [Date Column] IN
            DATESBETWEEN(
                [Date Column],
                BeginDate,
                EndDate
            )
        )
    )
    You would most likely need to have an inactive connection with your date table for this to work, but it should be able to accomplish your goal.