Forum Discussion

EvanAffleck123's avatar
EvanAffleck123
Regular Visitor
2 years ago
Solved

Dax formula for matrix

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 submitt...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

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,