Filtering Tabular Model Dimension Across Multiple Facts

Question

I am building a Tabular model in an educational environment, and am running into some difficulty around filtering the lookup table across multiple base tables.

I've read about bridge tables, which I believe I could use, but I have over a dozen fact tables, so the amount of corresponding bridge tables seems cumbersome to manage. Could I handle the filtering via DAX instead? I've been working to learn more DAX, but am new and stumped so far.

Here is a sample scenario of what I'm trying to accomplish: to simplify things, I have a dimension called Student which contains a row for each student. I have a fact called Discipline which contains discipline incident records for each student (1 per incident, and not all students will have a discipline incident). Also, I have a fact table called Assessment which contains assessment records for a test (1 per assessment completed by a student). Some students won't take this assessment, so they will have no corresponding scores.

When I model this data, in a Pivot Table, for example, to analyze a correlation between discipline and assessments, I bring in a measure called Discipline Incident Count (count of discipline incident records) and Assessment Average Score (average of assessment scores). I'm wanting to only view a list of students that have values for both, but I get something like the following:

Student Name --------Discipline Incident Count--------Assessment Average Score

Student A-------------------(blank)------------------------------85.7

Student B----------------------3-------------------------------(blank)

Student C----------------------2---------------------------------88.7

In this case, I would want my result set to only include student C, since they have a value for both. I have also tested handling the filtering on the application layer (Excel), by filtering out blanks in each column, but with the real data, which might have nested values and a large amount of data, doesn't seem to be working well.

Thanks for any feedback!

greggyb greggyb · Accepted Answer · 2015-12-03T19:07:39

So, filtering this in the application layer is probably your best bet.

Otherwise, you'll have to write your measures such that each one checks the values of all other measures to determine whether to display.

An example for the sample you've shares is as follows:

ConditionalDiscipline:=
SWITCH(
    TRUE()
    ,ISBLANK( [Assessment Average Score] )
    ,BLANK()
    ,[Discipline Incident Count]
)

SWITCH() is just syntactic sugar for nested IF()s.

Now, this measure would only work when your pivot table consists of [Discipline Incident Count] and [Assessment Average Score]. If you add another measure from a new fact table, the presence of a value for the student on the pivot table row will have no effect on the display of that row.

You'd need a new measure:

ConditionalDiscipline - version 2:=
SWITCH(
    TRUE()
    ,ISBLANK( [Assessment Average Score] )
    ,BLANK()
    ,ISBLANK( [Your new measure] )
    ,BLANK()
    ,[Discipline Incident Count]
)

Now this version 2 will only work if both [Assessment Average Score] and [Your new measure] are non-blank. If you use this version 2 in the sample you've posted without [Your new measure], then it will still return blank for the students that have no entry for [your new measure].

Thus, you'll need one version of this for each possible combination of other measures that would be used with it.

There is no way to define "the set of all other measures currently being evaluated in the same context" in DAX, which is what you really need to test against for the existence of blanks.

You could, rather than using a SWITCH(), just multiply all the other measures together in a single test in IF(), because multiplication by blank yields blank. This does nothing for the combinatorial number of measures you'll need to write. You've got a lot of options for exactly how you define your test, but they all would end up needing the same absurd number of separate measure definitions.

I'd also like to address another point in your question. You mentioned using a bridge table. A bridge is only necessary when you implement a many-to-many relationship between dimension tables. You are not implementing this. You have several facts with a single conformed dimension. The conformed dimension is essentially a bridge between your facts, each fact existing in a many-to-many relationship with the other facts, based on the StudentKey. Any bridge table is superfluous.

The long story, shortened, is no. There are not introspective facilities in DAX sufficient to do what you want. Handle it at the application layer.

Filtering Tabular Model Dimension Across Multiple Facts

1 Answers