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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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!:
The Definitive Guide to Power Query (M)

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors