Complicated Distinct Count

[neil37]

Hello, 

I am having issues accurately counting occurences of specific crimes due to identical rows that cannot necessarily be removed by "removing duplicates" because the CR, CRno, and Caseno may be the same but the offense may be different. 

See Image. 

For example: CR is the index column; however, there are more than one CR's when the Offense is different.

I want to create a column that has an accurate count of all offenses. DISTINCTCOUNTNOBLANK works okay for a measure, but not sure how to create a new column within my table (or new table) that considers what I mentioned above. 

In the screenshot example the occurences would be:

Criminal Arrest Warrant: 1
Robbery: 1
Theft - Motor Vehicle - 1

Tresspass of Real, et al - 1

Resisting . Interfering w/ Police - 1

Obstruct / Tampering et. al - 1

Thank you!

[EricVieira]

To create a new column in Power Query (M language) that accurately counts unique offenses per `CR`, you can use the following approach:

### Steps:

1. **Add a Custom Column**:
Use this formula to count unique offenses:

```m
Table.AddColumn(
PreviousStepName,
"Unique Offense Count",
each Table.RowCount(
Table.SelectRows(
PreviousStepName,
(row) => row[CR] = _[CR] and row[Offense] = _[Offense]
)
)
)
```

- `PreviousStepName`: Replace with the name of the previous step in your query.
- This filters rows by matching `CR` and `Offense` to the current row, then counts them.

2. **Optional: Group Data**:
If you need a summary table:

```m
Table.Group(
PreviousStepName,
{"CR", "Offense"},
{{"Unique Count", each Table.RowCount(_), type number}}
)

[William_Scott]

Great question! For your issue, it seems you need to create a calculated column that groups by the offense type while keeping your index column (CR) intact. One way to handle this is by using DAX with the SUMMARIZE function to create a new table that aggregates the offenses based on unique CR and offense type combinations. Alternatively, you can use GROUPBY if you're working directly within Power BI.

For further reference, you might also want to explore Virginia arrest warrants or similar records to cross-check how categorization of offenses is handled in real-life datasets. Here's a resource that might be helpful:  VA Arrest Warrent. This could provide some useful insights into structuring your data accurately. Let me know how it goes!

[Fowmy]

@neil37 

You can create a measure to count the distinct Offence count:

Offense Count = COUNTROWS(VALUES(Table[Offense]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AlB]

Hi @neil37 

You need to explain it a bit more. You want a calculated column in that table?? What for, if you already have a measure that gives you the right result? If you want the calculated column, do the numbers provided mean that you want the given value in each row that has that offense? For instance, you would want all rows where Offense is "Theft - Motor Vehicle" to have a - 1 in the new column? What is the logic for that number? Please show the esample table with the column you want added and the expected results, to clarify

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[neil37]

Hi! Thank you for the quick response!

The reason I cannot use the Dinstinctmeasure that I already created  (e.g., =DISTINCTCOUNT('tablename'[caseno])) because the new measure I am creating requires a Table Column (see below):

I am attempting to calculate the crime count last year, as well as the Difference between the Current Year and Last Year. I have the measures created but the caseno column is not distinct in the data so the sums are incorrect.

I am a bit new to PowerBI so if you have a different formula to use to calculate Last years Crime Count using a distinct count measure and not columns, I am open to suggestions! 

Thank you for your help!!

[neil37]

Ultimately, the new calculated column within our table would have a 1 or 0 if that specific row was counted as "distinct" when you instruct PowerBI to "Count(Distinct) within a visualization (That is how we have determined  the most accurate way to count the number of unique crime counts). If that does not make sens,e please let me know and I will try to explain it better.  @AlB  Thank you!!

[AlB]

@neil37 

Sorry. I don't get it 🤔

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers