Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Line Chart is not displaying correct trend

Hi,  I am trying to create a trend analysis for grade-based user selection on the department slicer. I created two measures to get the employee distinct count and the grand total of the selected department.  When I select ALL department, the chart/trend shows the correct information.  When I select 1 department, it displays 100%.   Any idea why?  Can anyone help?  

 

Here are my measure for the distinct count (numerator):

Count of Selected Filter by Grade =
VAR currentDate =
MIN ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT (Fact_Gender_Race[Employee ID]),
FILTER(
Fact_Gender_Race,
COUNTROWS (
FILTER (
RELATEDTABLE (Fact_Gender_Race ),
Fact_Gender_Race[Salary Effective Date]<= currentDate
&& (
ISBLANK (Fact_Gender_Race[AdjEndDate])
|| Fact_Gender_Race[AdjEndDate] >= currentDate
)
)
)
> 0
)
)

 

Here is my Grand Total measure (denominator).  Mostly the same as above, accept I added AllSelected function.

Grand Total BOM Selected OBS by Grade =
VAR currentDate =
MIN ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Fact_Gender_Race[Employee ID] ),
FILTER (
ALLSELECTED(Fact_Gender_Race),
COUNTROWS (
FILTER (
RELATEDTABLE ( Fact_Gender_Race),
Fact_Gender_Race[Salary Effective Date]<= currentDate
&& (
ISBLANK (Fact_Gender_Race[AdjEndDate])
|| Fact_Gender_Race[AdjEndDate] >= currentDate
)
)
)
> 0
)
)
  • Anonymous's avatar
    Anonymous
    7 years ago

    Posting the solution that works.  Thanks Darek!

    Grand Total =
    CALCULATE(
    [Count Filter by Grade],
    ALLSELECTED( 'Dim_Employee OBS' ),
    ALL(Dim_Employee[Grade])
    )

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago


    Here is what my line chart looks like when I select one department, one grade (24)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Please send a snapshot of our model.

       

      One thing I can tell you for sure: your DAX might be working correctly but it's much, much more complex than it should be. What's the downside of this? Well, it's not as fast is it could be and it's not as easily understandable as it could be.

       

      Best

      Darek

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        [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