Auto-exist and duplicated measure problem when using two Dim tables in a visual

[Nyansapo]

Dear PBI community, 
I searched a lot about this issue on the forum and elsewhere, and I couldn't find the solution. Here is the situation. 
Let's first present the data. I created a simple fake data model to illustrate the problem I'm encountering. 

I have four tables in my model 

Three dimension tables :

• Interviewer
• Office
• Calendar

One fact table :

• Transactions

Here is the Interviewer table (dim)

Interviewer ID	Office ID	Interviewer Name
1	1	Paul
2	1	John
3	2	Anne
4	2	Gabriel
5	3	Alain
6	3	Chrystine
7	4	Marie
8	4	Steve

Here is the Office table (dim) :

Office ID	Office name
1	Montreal
2	Ottawa
3	Toronto
4	Vancouvert

Here is the calendar table (dim) :

Date

01-Nov-23

02-Nov-23

03-Nov-23

04-Nov-23

05-Nov-23

Here is the Transaction table (fact) :

Transaction ID	Transaction date	Interviewer ID	Office ID	Sample Unit ID	Response Outcome
1	01-Nov-23	1	1	1000	0
2	01-Nov-23	2	1	1000	1
3	01-Nov-23	3	2	1001	0
4	01-Nov-23	4	2	1004	0
5	02-Nov-23	5	3	1001	1
6	02-Nov-23	6	3	1004	1
7	02-Nov-23	7	4	1003	1
8	02-Nov-23	8	4	1003	0
9	03-Nov-23	3	2	1003	0
10	03-Nov-23	4	2	1002	1
11	03-Nov-23	5	3	1006	0
12	03-Nov-23	6	3	1001	1
13	04-Nov-23	2	1	1002	0
14	04-Nov-23	3	2	1004	0
15	05-Nov-23	2	1	1006	0
16	05-Nov-23	3	2	1005	1
17	05-Nov-23	4	2	1008	1
18	05-Nov-23	5	3	1001	0
19	05-Nov-23	6	3	1005	1
20	05-Nov-23	7	4	1007	0
21	05-Nov-23	8	4	1008	0
22	05-Nov-23	6	3	1007	0
23	05-Nov-23	2	1	1006	1

Here is my data model :

Each Interviewer belongs to only one Office. Each interviewer have some transactions in the Transaction table.  

My basic measure is the number of responses by Interviewer. The number of response is defined as the number of unique Sample Unit ID for which we have Response Outcome = 1, which correspond to the following measure : 

Number Responses = 
CALCULATE(
    COUNTROWS(
        VALUES(Transactions[Sample unit ID])
    ),Transactions[Response Outcome] = 1
)

With this measure, I can create the simple table below that works properly :

The problem comes when I want to add the total number of response by Office in the same table. I use the following measure to do so : 

Number Responses Office = 
CALCULATE(
    [Number Responses],
    ALLSELECTED(Interviewer)
)

My problem may be in this formula, but I need to use ALLSELECTED() filter function because I need to be able to modify this measure from the external slicers. Here is the result I get when I add this measure in my table : 

As you can see, since I'm using two columns from two different dim tables, the Auto-Exact feature is disabled and I get the full cardinality of the tables Interviewer and Office (all possible combinations). The values of the measure "Number Responses Office" are accurate but it created a lot of impossible combinations of Interviewer and Office. How can I avoid this ? I don't want to manually filter out the rows where "Number Responses" = 0, because an interviewer may have no response, ans still be a valid data point. I would like to correct my measure so that it do not create all the impossible combinations of interviewers and offices. 

You can acces my PBIX fiel here : 

https://drive.google.com/file/d/1SoKd0ze7xRII27cY5vtgNDkavOaiSZID/view?usp=sharing

Please let me know if the link doesn't work. 

Any help will be much appreciated!

Thanks, 

[lbendlin · Accepted solution]

you don't really need DAX for that.  But a small change to the data model is required.

[lbendlin · Accepted solution]

you don't really need DAX for that.  But a small change to the data model is required.

[Nyansapo]

Hi @lbendlin ,

Thanks a lot for your quick reply! Your solution definitly works in this context, but here is my question : How if I would not have had Office ID in my Interviewer dim table? Let say we wouldn't know who is working where, but would only have the actual transactions in the fact table. I would like to be able to set my measure or my visual so that Power BI do not create row conbinations of Interviewers and Offices that do not exist in the fact table. This is a situation I often encounter. Is there any ways to do that?

I can of course extract the data from the fact table and feed it into my dim table, but would really like to avoid this kind of manipulation.

Thank you very much! 🙂

[lbendlin]

The data model needs to follow the business scenario.  If your business scenario changes then you need to adjust your data model accordingly.

[Nyansapo]

Hi @lbendlin , 
Thanks for your reply. Well, I made a mistake by adding the field Office ID to the Interviewer table. I did that only to show clearly that each Interviewer belong to an office. But in reality, in my real data, I don't know the office to which an interviewer belong, and I can only rely on the fact table. 

So in the specifc business scenario I'm refering to, what would you suggest? 

I'm sure there are a lot of scenario like this one, and I guess these is a relatively easy solution to fix this problem, but can't find it... 

Thank you very much, 

[lbendlin · Accepted solution]

But in reality, in my real data, I don't know the office to which an interviewer belong

Then you cannot include both in the same visual.

[Nyansapo]

Hi @lbendlin ,
Thanks for the input, I really thought it was possible, I thought there would be a way to get this information from the fact table (without explicitly populating the dim table with it), but now that I think about that, your answer make sense. 

Thanks a lot for your time and information, you got me out of my misunderstanding! Really appreciate! 🙂