Forum Discussion
Dax formula for matrix
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
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
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
Wonderful! That worked. Thanks Anonymous