cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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. 

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors