topic Re: Dax formula for matrix in DAX Commands and Tips

Dax formula for matrix

EvanAffleck123 — Mon, 05 Aug 2024 20:38:49 GMT

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

Re: Dax formula for matrix

DataNinja777 — Mon, 05 Aug 2024 21:22:19 GMT

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,

Re: Dax formula for matrix

Anonymous — Tue, 06 Aug 2024 06:05:03 GMT

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

Re: Dax formula for matrix

EvanAffleck123 — Tue, 06 Aug 2024 16:33:33 GMT

Hi Anonymous 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

Re: Dax formula for matrix

Anonymous — Thu, 15 Aug 2024 07:16:08 GMT

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

Re: Dax formula for matrix

EvanAffleck123 — Thu, 15 Aug 2024 21:24:24 GMT

Wonderful! That worked. Thanks Anonymous