Forum Discussion
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)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)))
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)
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 selectionIt 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 )?
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 )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?
