Value is not computed if row is missing

[Chris2016]

Hi,

I have the following issue in a table where Projects, Users assigned to projects and User Level are displayed:

Project	User	User Level
A	1	Junior
A	2	Senior
A	3	Medium
A	4	Junior
A	5	Medium
A	6	Senior
B	7	Junior
B	8	Senior
B	9	Junior
B	10	Senior
C	11	Senior
A	12	Junior
A	13	Senior
C	14	Senior
C	15
B	16
B	17	Senior
	18
	19
	20

I need to create a matrix where I can show if all existing projects have at least 3 users assigned per each User Level. Basically I am creating a measure to display the deltas per each project that does not have at least 3 Users per each User Level.

The issue is that the table is missing some rows for User Level, and also some Users do not have a User Level specified. I am flagging the non-specified User Levels as "Missing Level" in the calculation, but I do not have a solution for the cases where the table is not containing a User Level (for example, project B is missing User Level "Medium").

I tried calculating a new table using ADDMISSINGITEMS and SUMMARIZECOLUMNS, but that is not producing the missing rows in User Level per project (at least not by following the syntax specified in the official documentation ADDMISSINGITEMS function (DAX) - DAX | Microsoft Learn)

My calculations are:
Calculated column:

# Users per User Skill =
var blankproj = 'Table'[Project]= BLANK()
return
IF(blankproj, BLANK(),
CALCULATE ([# Users],ALLEXCEPT('Table', 'Table'[Project], 'Table'[User Level])))

Measures:

# Users = DISTINCTCOUNT('Table'[User])

 

# Missing Users per Project-User Level = 
var projnotblank = SELECTEDVALUE('Table'[Project])<> BLANK()
Var Levelnotspecified = SELECTEDVALUE('Table'[User Level]) = BLANK()
var result =
SWITCH(TRUE(), projnotblank && Levelnotspecified, "Missing level", projnotblank && MAX([# Users per User Skill])<3,3-MAX([# Users per User Skill]))
return result

These are the results:

In  the # Users matrix, we see that only Project A - Junior and Senior, and Project B - Senior have the desired number (3) of Users per those User Levels. In the below matrix, where the deltas are shown we see that number 3 is missing in Project B - Medium and Project C - Junior and Medium, because those User Level rows do not exist in the table.

Any idea how I can go about to create a matrix that displays the deltas even for those missing rows?

Many thanks!

[Zang_Mi · Accepted solution]

Hello @Chris2016,

Since there are missing rows in the fact table, I will create two dimension tables to enable returning values for those missing rows in the matrix.

New calculated tables:

/* Generate a dimension table keeping blank values */
DimProject = DISTINCT('Table'[Project]) 
or
/* Generate a dimension table excluding blank values */
DimProject = FILTER(DISTINCT('Table'[Project]), 'Table'[Project] <> BLANK())

/* Generate a dimension table keeping blank values */
DimUserLevel = DISTINCT('Table'[User Level]) 
or
/* Generate a dimension table excluding blank values */
DimUserLevel = FILTER(DISTINCT('Table'[User Level]), 'Table'[User Level] <> BLANK())

And relate then with the fact table, then we can use data fields from the dimension tables as rows and columns in the matrix visual.

New measure:

# Missing Users per Project-User Level = 
var projnotblank = SELECTEDVALUE('DimProject'[Project]) <> BLANK()
Var Levelnotspecified = SELECTEDVALUE('DimUserLevel'[User Level]) = BLANK()
var result =
SWITCH(TRUE(), projnotblank && Levelnotspecified, "Missing level", projnotblank && MAX([# Users per User Skill])<3,3-MAX([# Users per User Skill]))
return result

Result:

[Chris2016]

This is great, thanks so much for your help, much appreciated!