cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Giavo Regular Visitor
Regular Visitor

DAX: SELECTEDVALUE with Dates

Hello all,

 

INTRO:

i have a Relational model with 3 tables:

1)Project Milestones table containing Project Codes and 4 dates per each project (Date1, Date2, Date3, Date4)
2)Projects Table containing unique project codes

3)Locations table containing Locations and related Project codes

 

I have a rule in Project Milestones table in order to determine if a project is ACTIVE in a certain year and it is as follow:

Column:

Active in 2018 = IF((YEAR(Date 1)<=2018 || YEAR(Date 2)<=2018) &&
                                 (YEAR(Date 3)>= 2018 || YEAR(Date 4)>=2018)."Active"."Not Active")

 

This rule gives me all the ACTIVE Projects in 2018 displayed per Location.
PROBLEM:
I want the user to be able to select a YEAR himself in order to see all the active projects for the selected year; (not fixed 2018 anymore)

i tried this:
i have created a new Calendar table containing dates and applied this rule:

Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
IF((YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
                                 (YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)."Active"."Not Active")

Apparently SelectedYear=SELECTEDVALUE('Calendar'[Year]) doesn't work properly. Do you know how can i improve this rule ?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: DAX: SELECTEDVALUE with Dates

@Giavo I am just looking for sample data not actual data, but anyway I guess you are looking for this..

 

Test122Count = 
VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
VAR _Temp = FILTER(Test122,
((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
VAR _Temp1 = IF(COUNTROWS(_Temp)>0,1,0)
RETURN SUMX(_Temp,_Temp1)

image.png



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

Proud to be a Datanaut !





richbenmintz
Advisor

Re: DAX: SELECTEDVALUE with Dates

Hi @Giavo,

 

I think the following formula should do what you expect, essentially it will count the rows on the 'Project Milestones' table where the conditions are met based on the selected value

 

Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
Calculate(CountRows('Project Milestones'), FILTER('Project Milestones', (YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
(YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)))
11 REPLIES 11
Super User
Super User

Re: DAX: SELECTEDVALUE with Dates

@Giavo Please try this as a "New Measure"

 

Sample Data:

 

image.png

 

Measure:

 

Test122 = 
VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
VAR _Temp = FILTER(
                                Test122,
                                ((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
RETURN IF(COUNTROWS(_Temp)>0,"Active","NotActive")   

image.png



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

Proud to be a Datanaut !





Giavo Regular Visitor
Regular Visitor

Re: DAX: SELECTEDVALUE with Dates

Thank you for your help, it is useful but howewer it's not working 100%

If i put 2 columns in a table i have this result,
Project,          Active
Project1      Not Active
Project2      Active
Project3      Active

So apparently it works. But when i want to count per each year the number of Active Project it doesn't work anymore and i see this result :
Year , Count Projects

1999      199

2000      199

2001      199

2002      199

2003      199

I'm wondernig why ? Per each project it tells if the project is Active or not, but if i count the number of projets per year it doesn't work anymore

Super User
Super User

Re: DAX: SELECTEDVALUE with Dates

@Giavo Could you please post the sample data and expected output, which will help us to understand the scenario in more detail.


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

Proud to be a Datanaut !





Giavo Regular Visitor
Regular Visitor

Re: DAX: SELECTEDVALUE with Dates

Unfortunately i can't share the dataset, But basically i would like to count per each year the number of Active Projects so:

Year   Count Active Projets
1999          21

2000          39

2001         10

2002          2

Super User
Super User

Re: DAX: SELECTEDVALUE with Dates

@Giavo I am just looking for sample data not actual data, but anyway I guess you are looking for this..

 

Test122Count = 
VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
VAR _Temp = FILTER(Test122,
((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
VAR _Temp1 = IF(COUNTROWS(_Temp)>0,1,0)
RETURN SUMX(_Temp,_Temp1)

image.png



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

Proud to be a Datanaut !





Community Support Team
Community Support Team

Re: DAX: SELECTEDVALUE with Dates

Hi @Giavo

As tested, PattemManohar's solution is helpful, could you check it on your site, if you have problem please don't hesitate to ask.

3.png

 

Best Regards

Maggie

 

Highlighted
Giavo Regular Visitor
Regular Visitor

Re: DAX: SELECTEDVALUE with Dates

I did exactly the same thing, still not working. Per each year i have the same number of Projects (total of 296), even if i select a certain year i still have the same number (296). if i don't select a year then i still see the same 296 per each year.

I'm using Power BI Report Report Server Desktop August 2018.

Is it possible that you have connected the DimDate table to your Test table ? or there's no relationship at all ? In my case they are not connected an looks like because of this there's no reaction if i filter the year, i mean:

If i don't filter any year, then i see per each year 296 projects, but if i select only one year i still see 296 projects per that year. The measure is not adapting to the year selection 

Giavo Regular Visitor
Regular Visitor

Re: DAX: SELECTEDVALUE with Dates

just replied

Giavo Regular Visitor
Regular Visitor

Re: DAX: SELECTEDVALUE with Dates

It worked somehow after closing and reopening power bi. Thank you a lot Manohar !!!!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 2,441 guests
Please welcome our newest community members: