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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Giavo
Helper III
Helper III

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

@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 PBI Community Champion




View solution in original post

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


I hope this helps,
Richard

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

Proud to be a Super User!


View solution in original post

12 REPLIES 12
v-juanli-msft
Community Support
Community Support

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

 

just replied

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


I hope this helps,
Richard

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

Proud to be a Super User!


PattemManohar
Community Champion
Community Champion

@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 PBI Community Champion




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

@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 PBI Community Champion




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

@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 PBI Community Champion




What if I wanted to select a date range instead of passing the year in the Selectedvalue function. In my case, I have created a measure to get the Active employee count when I select the date as mentioned below

 

Active emp =
VAR _currdate =
SELECTEDVALUE ( 'edw DateDimension_Vw'[Date_Dt] )
var _firstStartdate = MIN('edw EmployeeHistoryDimension_Vw'[EffectiveStartDate])
VAR _employees =
CALCULATE (
COUNTROWS (
FILTER (
'edw EmployeeHistoryDimension_Vw',
'edw EmployeeHistoryDimension_Vw'[EffectiveEndDate] >= _currdate
&& 'edw EmployeeHistoryDimension_Vw'[EffectiveStartDate] <= _currdate
)
),
'edw EmployeeHistoryDimension_Vw'[StatusDescr] = "Active"
)
RETURN
IF ( ISBLANK ( _employees ), 0, _employees )

 

 

Manjari_0-1656566885345.png

 

 

Now I would like to select a date range where(from date>hire date and to date between the effective start date and effective end date) how can I achieve this requirement?

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

Manohar,

thank you again for the solution you have provided me. My Projects Milestones table has dublicate values, and it is connnected to another table Unique Projects where i need to count Projects from. I was expecting the solution to work automatically but it didn't. Are you able to help me with the solution (so counting the Projects from Unique Projects )?

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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