Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

data_rahul_0-1680742507285.png

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

data_rahul_2-1680742881450.png

 

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.

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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.

CNENFRNL_0-1680843130870.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
Alex_Sawdo
Resolver I
Resolver I

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. 
CNENFRNL
Community Champion
Community Champion

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.

CNENFRNL_0-1680843130870.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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.

amitchandak
Super User
Super User

@Anonymous , These approaches can help

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Anonymous
Not applicable

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.