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
PBI Report Builder - removing duplicate values in parameter dropdown
I'm currently working through building a paginated report with PBI Report Builder, but a small issue came up. One of my parameters is called Spend Year. Currently, for the sake of working with a faster loading file, I filtered this down to only include the year 2023. However, the issue with this is that the parameter dropdown is displaying multiple options for 2023. I believe it's displaying a 2023 for each row in the dataset that contains one. Having multiple options for the same thing just isn't practical, so I've been looking for a way to have the parameter dropdown display distinct options. I managed to find this article: https://www.c-sharpcorner.com/article/remove-duplicate-filter-values-from-ssrs-parameter-drop-down/. While this matches my situation, the VB code that he writes is for String values. I'm working with integers. I tried my best to adapt his code to work for integers (you can find the code below), but after following through everything, my Spend Year parameter is now greyed out with no selectable values. I was sure to configure the available values as detailed in the article. Does anyone happen to have any idea how I should go about this? If it's useful to know, my data was pulled via a DAX query. Thank you in advance! My version of the code: Public Shared Function RemoveDuplicates(parameter As Parameter) As Integer() Dim items As Integer() = parameter.Value Array.Sort(items) Dim k As Integer = 0 For i As Integer = 0 To items.Length - 1 If i > 0 AndAlso items(i) = items(i - 1) Then Continue For End If items(k) = items(i) k += 1 Next Dim unique As Integer() = New Integer(k - 1) {} Array.Copy(items, 0, unique, 0, k) Return unique End FunctionAuto-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!
