Measure count in matrix independent from (external) row filter

[Anonymous]

Im gonna simplify my model (hoping the solution still translates to my complex case..):

I have 2 tables that are related via "ID" column, each table also has a "name" column.

Here are possible combinations in a table visualization showing all the columns:

I made a matrix visualization with rows for t1[name].

I created a measure M1 counting the distinct ID occurences (which in row t1[name] context are A=2 B=1 C=2), this works fine.

Now, when i want to include a distinct count of ID occurences for t2[name] here is my problem: it will apply the row context (external filter) from t1[name], meaning my created measure M2 gives me e.g. for my "C"-row: C=1. Which makes sense in the row context, however what i want M2 to do is give me C=3 (ALL occurences of C).

Is this theoretically even possible? I've tried a bunch of stuff working with CROSSFILTER and ALL functions, creating a new Name table to relate the tables by Name just for M2.. Nothing worked and I just can't figure it out 😞

[DataNinja777 · Accepted solution]

Hi @Anonymous 

I've prepared your requested output as shown below:

In order to get this, I created a dimension table of names which have one to many relationships with 't1'[name] and 't2'[name], and instead of using the 't1'[name] on rows of the visualizaiton, I used name dimension table's [name] column. 

Data model is as shown below with two fact tables and two dimension tables. 

I attach the pbix file in the link below:

t1[name], t2[name].pbix

[Anonymous]

I've tried to set up the ID & Name tables and create the relationships. Unfortunately implementing this break too much of my previous work (visualizations, measures etc.). I give up at this point and will use seperate matrizes or sth. I'll accept your solution anyway because it answers the question as it was posed.

Thanks again!

[Anonymous]

Hey Sakiko,

thanks, I will try how this works in my case. I basically have 3 tables that are related. I have to generate the ID table as well first and see how it works with the relations (I must have a connection to my Date table as well). Will give feedback later.

Best regards

Felix