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!

[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