Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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):
Here is my Grand Total measure (denominator). Mostly the same as above, accept I added AllSelected function.
Solved! Go to Solution.
Posting the solution that works. Thanks Darek!
Here is what my line chart looks like when I select one department, one grade (24)
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.
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.
Just remove the RETURN keyword from the measure that errors out...
Best
Darek
Hi Darek, Tried that. It didn't produce the result I want.
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
Posting the solution that works. Thanks Darek!
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
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
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
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
[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
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