cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
)
)
1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: 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])
)

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

Re: Line Chart is not displaying correct trend

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

 

 

Super User IV
Super User IV

Re: Line Chart is not displaying correct trend

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
Not applicable

Re: Line Chart is not displaying correct trend

datamodel2.PNG

 

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.

line.PNGline2.PNG

Super User IV
Super User IV

Re: Line Chart is not displaying correct trend

[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

Super User IV
Super User IV

Re: Line Chart is not displaying correct trend

You could also try to remove filters from Department if the solution I gave you does not do what you want. From what you sent it's not clear whether the rows in the matrix must add up to 100% or the columns...

 

Also, I'd suggest you read upon ALLSELECTED() because it's a very COMPLEX function and using it correctly does require some knowledge.

 

Best

Darek

Anonymous
Not applicable

Re: Line Chart is not displaying correct trend

Hi Darek

 

Thank you!  I tried to use the DAX on grand total but it returns error.  RETURN function unexpected experession "return"  The syntax for ')' is incorrect. (DAX( VAR __currentDate = MIN ( 'Date'[Date] ) RETURN CALCULATE( [Count of Selected Filter by Grade], ALL( Dim_EmployeeGrade ) )))).


So, on the grand total I want to use the filter on the department (total of the department) and ignore selection by grade.  For example:  Department A is selected. 

                                  2019    %                  If Grade 20 is selected then my solution right now.

Grade 20 Count           8       8/28                 8/8      ---> it is trying to filter by grade as well.

Grade 21 Count          10     10/28               10/10

Grade 22 Count          10     10/28               10/10

Total Department A    28

 

Hope my explanation makes sense.

Super User IV
Super User IV

Re: Line Chart is not displaying correct trend

Just remove the RETURN keyword from the measure that errors out...

 

Best

Darek

Anonymous
Not applicable

Re: Line Chart is not displaying correct trend

Hi Darek, Tried that.  It didn't produce the result I want.

 

Super User IV
Super User IV

Re: Line Chart is not displaying correct trend

You can try to remove filters from departments instead of grades...

 

[Grand Total BOM Selected OBS by Grade] =
RETURN
    CALCULATE (
        [Count of Selected Filter by Grade],
        ALL( <YourDeparmentTable>, <YourDepartmentTable>[DepartmentName] )
    )

It's just not clear from what you've said so far which filters need to be taken off...

 

Best

Darek

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors