Dax formula for matrix

[EvanAffleck123]

Hi All,

I am working on a Power BI report with two tables:

Table A (Department): The main database, containing details of projects and departments.

Table B (Employee): Contains records submitted from an app, saved in SharePoint. This table includes details of departments, employee names, projects, etc.

My Goal: I am trying to build a matrix with:

Rows: Employee names from the Employee table.

Columns: Departments from the Department table.

Reason:I want to capture all department names to identify if an employee is not assigned to a particular department.

Relationships:

I have a many-to-many relationship between the Department column in both tables.

Cross-filter direction is set to single (Department filters Employee).

Current Measure:

Check = CALCULATE(MAX(Employee[Q/NQ]))

Output:

        Dep1  Dept2   Dept3

Alice   Q       Q        NQ

Bob    NQ     Q       

John   Q       NQ    

Ben                           Q

Harry  NQ     Q       

Chris   Q        Q        NQ

Issue: I want the empty spaces to display "N/A" instead. To achieve this, I modified the measure as follows:

Modified Measure:

Check =

VAR MaxValue = CALCULATE(MAX(Employee[Q/NQ]))

RETURN

IF(ISBLANK(MaxValue), "N/A", MaxValue)

Output:

          Dep1  Dept2 Dept3

Alice   Q        Q        NQ

Bob    NQ     Q        N/A

John   Q        NQ     N/A

Ben    N/A    N/A      Q

Harry  NQ     Q        N/A

Chris   Q        Q        NQ

Jim     Q        NQ      N/A

Kyle    Q        NQ      N/A

Larry   Q        N/A     N/A

Tom    NQ       Q         Q 

Problem: 

When I apply a filter using Department[Project], I get all employees from other projects as well, leading to additional rows in the matrix that should not be there.

How can I modify my measure or matrix setup to ensure that the output only includes employees related to the filtered project, while still displaying "N/A" for unassigned departments?
Expected output:

          Dep1  Dept2 Dept3

Alice   Q        Q        NQ

Bob    NQ     Q        N/A

John   Q        NQ     N/A

Ben     N/A    N/A      Q

Harry  NQ     Q        N/A

Chris   Q        Q        NQ

[v-zhouwen-msft]

Hi @DataNinja777 ,thanks for the quick reply, I'll add more.

Hi @EvanAffleck123 ,

I have assumed some data, if my understanding is wrong, please provide simple data.

Regarding your question, if you use the 'Department' field to establish the relationship between the two tables, use the 'Project' field in the 'Employee' table to filter.

Best Regards,
Wenbin Zhou

[EvanAffleck123]

Hi @v-zhouwen-msft @DataNinja777 for support
The problem is i have 2 slicers which i use to narrow my filtering, i already build a matrix with measure now if you try to filter out L2, i get D3 and D4 which is correct and employee names should be sam and alice only but im getting other names as well.
Im not able to share my pbix file here so i attached screenshots
Employee table

Dept table

Loc    Dept  Project

L1       D1      A

L1       D2      A

L1       D1      B

L1       D2      C

L2       D3      D

L2       D4      D

L3       D5      E

L3       D5      F

L4       D6      G

L4       D7      G

L4       D8      G

L4       D6      H

L4       D7      H

L4       D8      H

L4       D9      H

L4       D10    I

L4       D11    I

L4       D12    J

L4       D13    J

[v-zhouwen-msft · Accepted solution]

Hi @EvanAffleck123 ,

Sorry for taking so long to reply you. Regarding your question, after my testing, I think it is because the fields of the slicer are from different tables than the fields used in the visual.

My test.

Use the following DAX expression to create a measure

Measure = 
VAR _a = CALCULATE(SELECTEDVALUE('Employee table'[Employee]),ALL('Dept table'[Dept]))
VAR _b = CALCULATETABLE(VALUES('Employee table'[Employee]),ALL('Dept table'[Dept]),ALL('Employee table'[Employee]))
VAR _Q_NQ = MAX('Employee table'[Q/NQ])
VAR _result = IF(ISBLANK(_Q_NQ),"N/A",_Q_NQ)

RETURN IF( _a IN _b ,_result )
       

Final output

Best Regards,
Wenbin Zhou

[EvanAffleck123]

Wonderful! That worked. Thanks @v-zhouwen-msft 

[DataNinja777]

Hi @EvanAffleck123 ,

Based on my observation of your data model description, it seems advisable to create separate dimension tables for departments and projects. This will allow for a one-to-many relationship with your fact table, which records who is working on which projects within various departments.

In my over 10 years of data modeling experience, I have never needed to resort to many-to-many relationships, as they are not considered best practice.

Additionally, I assume that your fact table for employee project assignments includes date and time information, as an individual cannot work for multiple departments at the same time. Is this correct? If so, a calendar table should also be introduced as a dimension table.

Best regards,