IF(ISBLANK() ignores data model relationships?

[lloydthomas2399]

I am using 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]))))

However, it seems to ignore relationships and return '0' for every employee. The employees all have a department, and i have a filter set on the dashboard so it only shows me employees within the set department.

I therefore expect to see around 15 employees for this department. There are 2 employees whose training log count is 0 and therefore would not show in the data table if i just do a distinct count of 'Fact Core Training Log'[Training Log ID]. I want these to show as 0 so have used the measure above. 

It works in the sense it shows me these 2 employees and gives them a count of 0. However, it also lists every other employee in the database even though the filter is on to a department they are not in or related to in the model.

Is there a tweak I can make to the measure to correct this?

My filter is DepartmentID = '1' (coming from DimDepartment table).
Any help is appreciated.

[amitchandak]

@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)

[lloydthomas2399]

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.

[v-zhangti]

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.

[lloydthomas2399]

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.

[v-zhangti]

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.

[lloydthomas2399]

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.

[v-zhangti · Accepted 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.