cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ychang
Frequent Visitor

Show project if active during the selected year (slicer)

I apologize if this exists already, but I am trying to use the table visual to just show projects that have been active during the year selected using the slicer. In other words, I would like to only include projects with start year less than or equal to selected year and end year greater than or equal to the selected year. I have tried to do this by creating a column with the following dax: 

 

Active? = and(Project[Start_Date__c].[Year]<=value(SELECTEDVALUE('Reporting Period'[Year__c])),Project[End Date__c].[Year]>=value(SELECTEDVALUE('Reporting Period'[Year__c])))
 
I am starting to realize however that the values of a column probably does not respond to the selected slicer values. Does anybody know a workaround for this? Thank you
4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi @ychang ,

 

Duplicate table Reporting Period[Year_c],calling table 2,use a dax expression as below:

 

Table 2 = DISTINCT('Reporting Period'[Year_c])

 

 

Create relationships between  the 3 tables:

Annotation 2020-03-23 131424.png

 

Using a measure as below:

 

 

Measure = 
var a=IF(SELECTEDVALUE('Project'[Start_Date_c]) in VALUES('Table 2'[Year_c]),SELECTEDVALUE('Project'[Start_Date_c]),BLANK())
var b=IF(SELECTEDVALUE('Project'[End_Date_c]) in VALUES ('Reporting Period'[Year_c]),SELECTEDVALUE('Project'[End_Date_c]),BLANK())
Return
IF(a<>BLANK()&&b<>BLANK(),1,0)

 

 

Finally you will see:

 

Annotation 2020-03-23 130455.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Greg_Deckler
Super User
Super User

Turn it into a measure and use it as a filter perhaps?

 

Active? = and(MAX(Project[Start_Date__c].[Year])<=value(SELECTEDVALUE('Reporting Period'[Year__c])),MAX(Project[End Date__c].[Year])>=value(SELECTEDVALUE('Reporting Period'[Year__c])))

 

Do you really need VALUE? Are your years text? 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you for your reply! The year field is a text field - I was a bit lazy and did not convert it to number at the time but have done so since. I also have tried to make this into a measure and adding it as a field in the table visual, but it seems to be calculating across all data instead of just for a specific project. 

ychang
Frequent Visitor

As for the option of adding another table, the year field in the reporting period are not actually dates so what I did instead is just make another table with all the active dates for each project kind of like this:

 

With a table of projects like this...

ProjectStart DateEnd Date
Project A10/1/201910/3/2019
Project B9/20/201910/2/2019

I created another table that looked something like this:

Project

Active Date

Project A 10/1/2019
Project A 10/2/2019

Project A

 10/3/2019
Project B9/20/2019
Project B9/21/2019
Project B...
Project B 10/1/2019
Project B 10/2/2019

 

My thinking is that with this, I should be able to create a column/measure to check whether the related table contains a date from the year that is selected with the slicer, but I am wondering if this is feasible and if so what my next steps should be. 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors