Forum Discussion
Auto-exist and duplicated measure problem when using two Dim tables in a visual
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,
you don't really need DAX for that. But a small change to the data model is required.
But in reality, in my real data, I don't know the office to which an interviewer belongThen you cannot include both in the same visual.
6 Replies
- lbendlinSuper User
you don't really need DAX for that. But a small change to the data model is required.
- NyansapoFrequent Visitor
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! 🙂
- lbendlinSuper User
The data model needs to follow the business scenario. If your business scenario changes then you need to adjust your data model accordingly.