Forum Discussion
Line Chart is not displaying correct trend
Posting the solution that works. Thanks Darek!
Grand Total =CALCULATE([Count Filter by Grade],ALLSELECTED( 'Dim_Employee OBS' ),ALL(Dim_Employee[Grade]))
Here is my data model. There are two fact tables, but I am concentrating only on one fact table "fact_gender_race" which has a relationshipt to Dim_Employee_OBS for my department slicer.
In my matrix I have:
Year as column
Grade as Row
% measures as Values
So my matrix looks like below:
Select (Slicer): Department A
2012 2013 2014 2015 2016 .......
grade 20 8% 15% 20% 10% 11%
grade 21 20% 11% 5% 21% 6%
grade 22 15% 15% 7% 22% 5%
When the user select only the department, BI virtualize my data beautifully. When selecting both department and grade, that when it goes weary... The business requirement is to show trend by selected department, by selected grade.
[Count of Selected Filter by Grade] =
VAR __currentDate = MIN ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Fact_Gender_Race[Employee ID] ),
Fact_Gender_Race[Salary Effective Date] <= __currentDate,
OR(
ISBLANK ( Fact_Gender_Race[AdjEndDate] ),
Fact_Gender_Race[AdjEndDate] >= __currentDate
)
)
-- You should not leave AdjEndDate BLANK. It's much better
-- to put a date like DATE(9999, 1, 1) in there, especially
-- if you're not going to slice by the column and do not
-- join a Calendar table to it. Then your measure will be simpler:
[Count of Selected Filter by Grade] =
VAR __currentDate = MIN ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Fact_Gender_Race[Employee ID] ),
Fact_Gender_Race[Salary Effective Date] <= __currentDate,
Fact_Gender_Race[AdjEndDate] >= __currentDate
)
-- Best of all, it'll be faster as well.
[Grand Total BOM Selected OBS by Grade] =
RETURN
CALCULATE (
[Count of Selected Filter by Grade],
ALL( Dim_EmployeeGrade )
)
-- ALL( Dim_EmployeeGrade ) removes ALL filters placed
-- on Dim_EmployeeGrade. So the above calculates the
-- [Count of Selected Filter by Grade] as if no filter
-- were placed on Dim_EmployeeGrade. Is this what you wanted?Best
Darek