Find or Count Duplicates in Power BI Table
Hello, To find or count duplicates in a table, watch this Video. Sometimes you want to find duplicates to root the problem, sometimes for your analysis and sometimes, you'll need as condition for another calculations... https://www.youtube.com/watch?v=JI-QUASKuUw
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,
Count repeated values on three columns
Hello: I have a "Table" with three columns, A, B and C, filled with many different string values. I want to find how many rows of that table have all three columns equal to another row in the table. In other words, find rows with duplicate "A+B+C". In a visual I would need a number, indicating how many non unique values there are, combining the three columns. Is there a formula to achieve this or do I have to concatenate all three columns in one first and then somehow count the duplicates? Thank you very much and regards.
Find duplicates between "date selected" and "date selected" - 30 days
Hi! 🙂 I have date filters (slicer) for year, month and day in a model that uses a table with [ID] and [Date] I want to show the duplicates between the "Date selected" and ("Date selected" -30 days) This is the table: If i select "December 1st, 2022" it should only show these: Thank you!
