how to show related data without direct relationship to slicer

[teylyn]

Hi, I have data like the screenshot below.

the tblLocal has local data for student, course, standard and grade. The tblNational has columns for standardID, grade, and a nationwide value.  I have a lookup table called slicertable that is related to tblLocal via CourseID. This table is used to present a slicer with Faculty.

In the report, the user selects a Faculty in the slicer. The Matrix1 shows data from tblLocal, and shows only the standards in the selected Faculty. Fine.

My issue: How can I create the Matrix 2, that summarizes the highlighted StandardID rows and their Grade values from tblNational? I don't have the DAX knowledge to create that. 

The logic in words: filter tblNational to show all the StandardIDs that are currently showing in tblLocal, which is filtered by the slicer. 

data at the top , report at the bottom

[smpa01 · Accepted solution]

@teylyn  if you got to Matrix A,  you can do the following to get to Matrix B. I assumed there is no relationship between national and local and national and slicer

Measure = 
CALCULATE (
    SUM ( national[value] ),
    FILTER (
        national,
        national[national.standardID] IN VALUES ( local[local.StandardID] )
    )
)

[teylyn]

THANK YOU!! That was exactly what I need.

[grantsamborn]

Hi @teylyn 

I think 2 values in your Local matrix are incorrect:

StandardID: xxx - Grade: A - My result shows 1 whereas your's shows 2

StandardID: zzz - Grade: B - My result shows 1 whereas your's shows 0

Can you verify your numbers?

[teylyn]

Yes, you are right. I made a mistake in the manually configured matrix.

[Element115]

Isn't it just a matter, in your model, of linking your Dim table (aka slicer table) to tblLocal.CourseID and tblLocal.StandardID to tblNationalStandardID?  That way, the slicer will look up entries in tblLocal by CourseID, which selects also the respective StandardID, which in turn selects the StandardID in tblNational, making all these columns available to your matrix visuals.