Filter visual using related table

[Anonymous]

I have two tables, one is a calendar and the other is a list of job posting details. I am calculating the date a job posts is over a certain age, then using the calendar can make a trend chart showing how many aged posts there is month on month.

Table 1 looks like so *

Job Req	Responsibility	Open Date	Aged Date	Close Date
R12345	Standard	14/05/2022	24/05/2022	21/05/2022
R98623	Niche	14/05/2022	24/05/2022	10/06/2022
R23789	Standard	17/05/2022		23/05/2022
R10398	Standard	01/06/2022	11/06/2022	21/06/2022

And the Calendar is a list of dates with some calculations

Date	Aged Count
24/05/2022	2
25/05/2022	0
26/05/2022	0
27/05/2022	1

Using the Calendar date on the X-axis and Aged Count on the Y-axis I can create a bar chart showing the count of aged jobs per day.

I have created a relationship between the Calendar and Table 1 using the date column, however I am unable to add the Responsibility as a filter to the visual. How do I get the relationship to work where I can use the Responsbility as a filter for the chart made off the Calendar?

[Anonymous]

Hi @Anonymous 

NickolajJessen is right, disciplined modeling is the beginning of everything good. 

I changed the calculated column to measure and created slicer and filter, please check the PBIX file.

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Thank you for this however this captures the static date an even happens, whereas I'm trying to capture daily records over a period of time, a position that ages on 1/5/22 and closes on 6/5/22 would be counted on the 1st, 2nd, 3rd, 4th, 5th and 6th of the month. Upon reflection I see the question didn't outline this particularly clearly.

[NickolajJessen]

Well, the calendar table should be used as a calendar and nothing more. Making businesslogic calculated column on the calendar table is a big no-no.

The filter isn't woking, because you probably have a one-to-many relationship on your calendar to your table1 (As you should) but that means, that reponsibility from the downhill table (table1) does NOT filter the uphill table (calendar)

Do it in a measure in stead of a calculated column.
As a general rule: Make all calculation in measure, unless you can articulate a good and valid reason for not doing so.

If this post solves your problem, accept it as a solution
Appreciate your kudos