Find Unique and Duplicate Values Count in Power BI Based on Multiple Conditions

[viralpatel21]

Hello

I am trying to create the  column  "dentifying" to calculate duplicates based on Multiple conditions.. i.e. 

e.g. if the name has a duplicate but the match status has has "monitored" and " unmonitortesd" then the identifying column should say "yes".

Name	Match status	Identifying
a	Monitored	no
a	Monitored	no
a	Monitored	no
a	Monitored	no
b	Monitored	no
c	Unmonitored	no
d	Unmonitored	no
e	Monitored	yes
e	Monitored	yes
e	Unmonitored	yes
d	Monitored	yes
d	Unmonitored	yes
f	Monitored	no
h	Monitored	yes
h	Unmonitored	yes
h	Monitored	yes

[v-yiruan-msft]

Hi @viralpatel21 ,

I created a sample pbix file(see the attachment), please check if that is waht you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUfLNz8ssyS9KTQGy8/KVYnWoJJyEXTgZyAzNy8WUSMElkYpmUGVqMUFxVINgMik4dGBaDZNJw+6JDBwGZeA0CKuOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Match status" = _t, Identifying = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Match status", type text}, {"Identifying", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Match status", "Identifying"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

If the above ones can't help you, could you please provide your expected result base on your shared sample data? And which conditions it need to satisfy? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[viralpatel21]

Thank you for the solution. But i dont think i have correctly explained myself.

The identifying column is the column which i want to achieve.  

ie.e if the name has a duplicate but the match status has has "monitored" and " unmonitorted" then the identifying column should say "yes".

[v-yiruan-msft · Accepted solution]

Hi @Raghu26 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Identifying = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Match status] ),
        FILTER (
            'Table',
            'Table'[Name] = EARLIER ( 'Table'[Name] )
                && 'Table'[Match status] IN { "Monitored", "Unmonitored" }
        )
    )
RETURN
    IF ( _count > 1, "yes", "no" )

Best Regards 

[viralpatel21]

Thank you so much!!! it worked brilliantly!!