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.
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 ?
Solved! Go to Solution.
@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)
Proud to be a PBI Community Champion
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)))
Proud to be a Super User!
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.
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)))
Proud to be a Super User!
@Giavo Please try this as a "New Measure"
Sample Data:
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")
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
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)
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 )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |