Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am using the following measure:
Solved! Go to Solution.
Hi, @lloydthomas2399
Department table:
Number of training days =
VAR _N1 =
CALCULATE ( DISTINCTCOUNT ( 'Fact Core Training Log'[Training Log ID] ),
FILTER ( ALL( 'Fact Core Training Log' ),
[Department] = MAX ( 'Fact Core Training Log'[Department] )
&& [Employee] = MAX (Department[Employee]) ) )
RETURN
IF ( _N1=BLANK(), 0, _N1 )
Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@lloydthomas2399 , You have to control range or some other values
example
0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1)
Thanks for the reply, would you be able to structure your measure to suit my example please? I'm not interested in date it's just my training log and department table.
Hi, @lloydthomas2399
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhangti , thank you for your reply. Let me detail below for you.
So, as mentioned I have a FactTrainingLog table and several dimension tables. One dimension table DimEmployee another DimDepartment. I want to show, in a data table, the number of times and employee has completed a training course. Only one department are required to do this training course, so the dashboard will have a filter of DimDepartment = 'Flex'.
I therefore want to count on the TrainingLogID in the FactTrainingLog table. Doing so, gives me each employee in the flex department and the number of times they've done the training, but only if they've done it at least once, e.g:
Employee | Department | Count of TrainingLogID |
Person 1 | Flex | 12 |
Person 2 | Flex | 7 |
Person 3 | Flex | 6 |
Person 4 | Flex | 2 |
Person 5 | Flex | 1 |
Person 6 | Flex | 1 |
So doing a distinct count works fine, obviously. However, I need to show a count of 0 for those employees who have never done the training, so the table would look like so:
Employee | Department | Count of TrainingLogID |
Person 1 | Flex | 12 |
Person 2 | Flex | 7 |
Person 3 | Flex | 6 |
Person 4 | Flex | 2 |
Person 5 | Flex | 1 |
Person 6 | Flex | 1 |
Person 7 | Flex | 0 |
Person 8 | Flex | 0 |
So to acomplish the above, I tried to use the following measure:
Number of training days = CALCULATE(IF(ISBLANK(DISTINCTCOUNT('Fact Core Training Log'[Training Log ID])), 0, (DISTINCTCOUNT('Fact Core Training Log'[Training Log ID]))))
Now, in a way this works but incorrectly. Whilst it then gives me Person 7 & 8 in the table with a count of 0, it also brings in every other employee in the database and gives them a count of 0 and also attaches them to Flex department even though that is not their department? If I remove my department filter (DimDepartment = 'Flex') then every employee is attached to every department, e.g. Person 1 would then show in the table as
Employee | Department | Count of TrainingLogID |
Person 1 | Flex | 12 |
Person 1 | Red | 0 |
Person 1 | Blue | 0 |
Person 1 | Green | 0 |
So, the measure just seems to ignore the data model relationship/filter?
Your help would be appreciated.
Hi, @lloydthomas2399
You can try the following methods.
Number of training days =
VAR _N1 =
CALCULATE ( DISTINCTCOUNT ( 'Fact Core Training Log'[Training Log ID] ),
FILTER ( ALL ( 'Fact Core Training Log' ),
[Department] = SELECTEDVALUE ( 'Fact Core Training Log'[Department] )
&& [Employee] = SELECTEDVALUE ( DimEmployee[Employee] ) ) )
RETURN
IF ( ISBLANK(_N1), 0, _N1 )
Please try again to bring in the remaining Departments and see the results.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhangti , unfortunately this doesn't work. It still brings in employees not related to the department. I've edited your pbix and added Person 9, added them to 'Green' department and they still are showing when the flex department is selected...
Similarly, if you select 'Green' from the dept slicer, Persons 1-8 show in the table when they shouldn't as theyre Flex dept.
Hi, @lloydthomas2399
Department table:
Number of training days =
VAR _N1 =
CALCULATE ( DISTINCTCOUNT ( 'Fact Core Training Log'[Training Log ID] ),
FILTER ( ALL( 'Fact Core Training Log' ),
[Department] = MAX ( 'Fact Core Training Log'[Department] )
&& [Employee] = MAX (Department[Employee]) ) )
RETURN
IF ( _N1=BLANK(), 0, _N1 )
Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |