Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

 

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Best

Darek

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

Posting the solution that works.  Thanks Darek!

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

Hi Darek

 

So if I removed/unfiltered Department,  the % is based on total of all employee and not the selected department.  So I believe the department should be filtered.  The grade should not be filtered for the selected department. I can figured out how to write the formula when two different dimensions/tables are involved.    My department is from DIM_OBS table (my slicer).   My grade is from my Fact_Gender_Race table. 

 

Thank you for helping!

 

Emilia

Anonymous
Not applicable

Emilia,

 

Please bear in mind that in a proper design, which also makes it easy to create correct formulas, you should have a star schema - all your dimensions should hold attributes that you slice by and the fact table should ONLY hold your figures and keys to the dimensions to the point where you can hide all the columns in your fact table. Currently, your design is not proper since your fact table does contain something that you slice by. If you want to be on the safe side and avoid headaches in the future, please turn your model into a PROPER ONE. One more thing to say about the proper design is that if you keep all attributes in your dimensions, you'll be able to see all of the possible values, whereas if you keep them in your fact table, this might not be the case - you'll only see the ones that do have some entries in the fact table. This might be misleading.

 

Best

Darek

Anonymous
Not applicable

Hi Darek - So I made some adjustments and use grades from Dim_employee instead of from my fact table.  The chart seems to be working.  I also hide all unnecessary columns from my fact tables. 

 

Thanks

Anonymous
Not applicable

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

[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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors