How to count duplicate rows, sensitive to filtering, to be used while creating a column chart

[LowisC]

Hello Guys,

I have been trying so hard to come to a solution, but it seems impossible. I can't find any related topics, and when I find couple that seem familiar the final result is never the intended one.

So, I have table 1 with a "Document ID" column among other data, like for example column "Task Completion Date". My rows represent workflows, therefore, Document IDs may be repeated throughout "Document ID" column, since I may have many workflows for the same document ID.

I am working on a dashboard that is being filtered by "Task Completion Date", and I want to create a column chart that shows in X axis the number of repetitions existing after filtering (e.g., 1 [for those not repeated], 2, 3, 4, 5, etc...) and in Y axis the total number of Document IDs that experience such number of repetitions mentioned in X axis.

The difficulty lies in finding a way of doing this after applying the filtering in the dashboard. So far, I was able to create a measure that tells me how many times a Document ID is repeated after applying the filtering, but only by showing Document ID in X axis and the number of repetitions for each Document ID in Y axis, which gives me an unreadable column chart since I may have hundreds of different document IDs. I have also tried to create a calculated column which works fine to show the results on the table and tell me how many times each document ID repeats, the issue is that when I am using that column while creating the column chart, it is not sensitive to the filtering by "Task Completion Date", it will always give me the overall number of times a Document ID repeats itself in my table before filtering (raw data).

I hope I didn't get too confusing. Please let me know if I need to provide any further clarifications.

I would really appreaciate anyone's help.

[theov]

Hey there,

Check this out, it might be helpful. You can count duplicates and use as a condition etc...

https://www.youtube.com/watch?v=JI-QUASKuUw

[pradeep_kare12]

Go to the "Model" view in Power BI Desktop and click on the "New Measure" button in the "Modeling" tab. Then, use the following DAX formula to create a measure that counts the duplicate rows in your data, sensitive to filtering.

This is the DAX function -- DuplicateCount = COUNTROWS(FILTER(ALL('YourTableName'), 'YourTableName'[YourColumn] = EARLIER('YourTableName'[YourColumn])))

you can insert your table names in it.

[PetrovicJozef]

It cannot work.  EARLIER cannot be used in measure, only in calculated column. 

[LowisC]

@gmsamborn yes, it changes based on date filtering. The "Workflow Cycles" column is nothing more than a calculated column that tells me how many times a Document ID is duplicated, so I know how many workflows I have for each Document ID on my Raw Data Table. But when I filter by Task Completion Date, the workflows listed will change and some Document IDs that could be duplicated will no longer be duplicated, or if a Document ID had 5 worflows (rows) it may only have 3 workflows (rows) after filtering. That's why I need to have a way to basically calculate how many time the values in Column "Document ID" repeat when I filter and select a specific time period, so I can see how many workflows that Document ID went through in that period.

The "Workflow Cycles" column was created by myself in Power Bi in an attempt to get what I am looking for, but then discovered that calculated column are not affected/sensitive to filters used in visuals/dashboards, so it shows me always the number of repetitions for each Document ID calculated from the raw data table.

[gmsamborn]

OK.  That's what I thought.

I guess the next step would be to include the detail table with data that corresponds to your existing sample data if possible.

[LowisC]

@gmsamborn , could you be more explicit? My raw data table is very similar to the sample table shared before, but with a lot more data (rows), but my columns are "Document ID" and "Task completion Date". Am I missing something?

[gmsamborn]

OK.  I thought you had a separate table that had the actual "Workflow Cycles" that were be totalled in your calculated column in the table supplied.

My raw data table is very similar to the sample table shared before, but with a lot more data (rows), but my columns are "Document ID" and "Task completion Date".

Are those the only columns?  What about the Workflow Cycles?

Can you show me an exact example of the relevant columns in the table supplied?

[LowisC]

@gmsamborn, Yes I only have those 2 columns. Workflow Cycles is not part of the source data, I created it to try to get the result I was looking for, but unsuccessfully. I will delete that calculated column from the table as I have no use for it.

Long storyshort, I would like to be able to get the sample table previously shared (without the Workflow Cycles column) and use it to create a chart where I can se something similar with the charts I shared with you in my comments.

[gmsamborn]

OK @LowisC .  If you only have those 2 columns, would the "duplicates" be based on the rowcount per DocumentID?

[LowisC]

@gmsamborn correct. 

Each row of my table represents a workflow, meaning that if I have the same Document ID in, for examples, 4 rows, that document went through 4 workflows, each workflow at a diferent time. That's why I need to be able to filter by Task Completion Date and see during that time period how many documents went through 1, 2, 3, 4, etc. workflows. I would like to present that in a bar/column chart.

[gmsamborn · Accepted solution]

@LowisC 

Like you said it would probably just be a matter of changing the [Cycles] measure.  I just changed it to a rowcount and it seems to work.

Count duplicates - 2.pbix

[LowisC]

@gmsamborn AMAZING! It worked perfectly. Thank you very much for your time and patience. Really appreciate it!

[gmsamborn]

@LowisC 

I'm glad it worked.

[Sergii24]

Can you share with us your data sample and desired output as Excel chart or PowerPoint presentation? It would be great if you use the same sample you share with us, so we can validate a result 🙂

[LowisC]

Hello @Sergii24 , thanks for your prompt reply. See below data sample and some more additional information on the desired output.

Data sample:

Document ID	Task Completion Date	Workflow Cycles
GL-0211	24/06/2021	3
GL-0211	28/06/2021	3
GL-0211	29/06/2021	3
GL-0213	22/06/2021	2
GL-0213	25/06/2021	2
GL-0245	08/12/2021	1
GL-0262	22/11/2021	2
GL-0262	23/11/2022	2
GL-0287	22/03/2021	1
GL-0297	30/08/2022	4
GL-0297	20/07/2022	4
GL-0297	21/07/2022	4
GL-0297	16/08/2022	4
GL-0298	16/11/2021	2
GL-0298	03/12/2021	2
GL-0317	09/08/2022	1
GL-0318	12/01/2022	1

"Workflow Cycles" column was created by myself in Power BI using the following calculated column:

Workflow Cycles = CALCULATE(COUNTROWS('MLR'), FILTER('MLR', 'MLR'[Document ID]=EARLIER('MLR'[Document ID])))

The issue with this calculated column is that it displays the same result regardless of the filtering applied in the dashboard where I want to add the column chart.

I have also created the following calculated measure:

Workflow Cycles = CALCULATE(COUNT('MLR' [Document Number]))

The issue with the calculated measure is that when trying to apply the measure in a column chart, despite being sensitive to the filtering applied on the dashboard (filtered by "Task Completion Date"), I am only able to add the calculated measure to the Y axis, which is not what I want as I mentioned in my initial post, because I will get hundreds of Document IDs in X axis (which is unreadable in a column chart) and in the Y axis I will get the number of times each Document IDs repeats.

What I really want, and considering the data sample above, is to build a column chart where I am able to display the following:

X axis - Workflow Cycles (1, 2, 3, 4)

Y axis - Number of Document IDs that went throught 1, 2, 3 and 4 Workflow Cycles, respectively.

Hope this helps. Thanks very much in advance.

[gmsamborn]

Hi @LowisC 

Would something like this help?

/* Table */
Count = GENERATESERIES( 1, 1000, 1 )
/* Rename [Value] to [Count] and set aggregation to none. */


/* Measures */

Cycles = SUM( 'DataTable'[Workflow Cycles] )

Duplicates = 
VAR _Col = SELECTEDVALUE( 'Count'[Count] )
VAR _Table =
    SUMMARIZE(
        'DataTable',
        'DataTable'[Document ID],
        "@Cycles", [Cycles]
    )
VAR _DupeCol = 
    COUNTROWS(
        FILTER(
            _Table,
            [@Cycles] = _Col
        )
    )
RETURN
    _DupeCol

Count duplicates.pbix

You will probably want to change the date slicer.

[LowisC]

@gmsamborn thank you very much for your reply and for even sending an attachment with what you created. Really appreciate it.

What you did worked perfectly to put the info I wanted in both the X and Y axis, which is the workflow cycles. But the main problem remains, the workflow cycles are summing and that's not what I want. To give you a better understanding, the "Workflow Cycles" column in my main table was created by myself to basically tell me how many time each Document ID is repeated, which will basically tell me how many Workflows I have for each Document ID.

In the Raw Data Table that's fine, but the issue is that I need a measure that is able to calculate how many times a Document ID is repeated after applying a date filtering, so I know how many times between a specific date a Document ID have gone through a workflow.

I think you did the most difficult part, I guess now maybe what I need is a new measure to replace the "Cycles" measure you created with a measure that, instead of summing the value I have in the "Workflow Cycles" column (and we can actually forget this column), will count the duplicates after applying the date filtering. Basically, I need a measure that does the same that my "Workflow Cycles" calculated column does in my raw data table, but that calculates and is sensitive to the date filtering I am applying. Does this make sense?

Let give an example. If I filter my sample table above to show results between 01MAR2021 - 30NOV2021 we get:

Document IDTask Completion DateWorkflow Cycles

GL-0298	16/11/2021	2
GL-0287	22/03/2021	1
GL-0262	22/11/2021	2
GL-0213	25/06/2021	2
GL-0213	22/06/2021	2
GL-0211	29/06/2021	3
GL-0211	28/06/2021	3
GL-0211	24/06/2021	3

As you can see, the Workflow Cycles column no longer holds true, because that calculated column is calculating always based on the raw data table with no filterings. After applying the date filtering and from the filtered data above, my column chart should look like this:

3 Document IDs that appear once

1 Doc ID appears twice

1 Doc ID appears 3 times

Which basically tells me the number of Document IDs that went through 1, 2, 3, etc., workflow cycles in the period of time I've selected.

Hope this helps to further clarify. 

[gmsamborn]

Hi @LowisC 

I'm not sure what you mean by As you can see, the Workflow Cycles column no longer holds true, because that calculated column is calculating always based on the raw data table with no filterings.

Are you saying the number of Workflow Cycles changes over time?

I'm guessing you will need to include the detail table that originally had the Workflow Cycle information.  (ie. the table use in calculating your column [Workflow Cycles])

Let me know if it appears that I misunderstood the line quoted above.