auto-exist

2 Topics

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,
Solved
Nyansapo
2 years ago Place DAX Commands and Tips
1.7KViews
0likes
6Comments
After adding column with totals without a specific filter, blank rows appear. DAX adjustment needed
Dear members, Ok this is my first post, so, I don't know yet how to upload my example file, so I'll try to explain the problem as clearly as possible, but it all comes down to not understanding the way the filtering works and how to get the desired info, easily. So, I have the following 6 tables: Category table with category number and name; Client table with client number and client name; Date table; Employee table with employee name and number; Table for invoice lines with employee number, client number, category number, date and invoice amount; Table for written time with employee number, client number, category number, date, hours written and the amount that represents the written time (hours x price). The 6 tables are properly linked together. In the page in the report section, I enabled a filter for the first employee and a filter for a date range. So, I made a table in the report section with the following columns: Employee Name from the Employee table; Client name from the client table; Total hours from the written time table; Total amount to be charged from the written time table; Measure that retrieves the invoice amount without the Employee filter, but with a filter for the category embedded in the DAX formula. The measure mentioned under 5), has the following DAX code Invoice amount without Employee filter = CALCULATE( SUM('Invoice lines'[Invoice amount]), ALL(Employees[Employee Name]), Categories[Categorynumber] = "01") With the current configuration, I retrieve a table in which only the rows for the first employee are visible, but the rows are not limited to the clients that the first employee wrote time for. Instead it returns all clients that have invoice lines with category number "01". In my example, there is only one client for which the first employee did not write time for, but in my actual model, it returns multiple lines that are not relevant for the employee, because he did not write time on those clients. So, what I wanted to retrieve, was a table with the clients for employee 1 with the hours written and amount chargeable that is related to employee 1, compared to the total invoiced amount for that specific client of which the invoice lines only refer to category number "01", but instead of only calculating the invoiced amount that relates to employee 1, it should represent the invoiced amount for all employees. Of course when I export the table to Excel and manually the lines that I don't need, is also an option, but that is not as smooth as I would prefer, because I would prefer a way to compile the complete desired table without the excess rows that are not relevant for employee 1. So, anyone who has a working solution/method?
Solved
Anonymous
3 years ago Place DAX Commands and Tips
675Views
0likes
2Comments