Forum Discussion

viralpatel21's avatar
viralpatel21
Icon for Helper II rankHelper II
3 years ago
Solved

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

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" an...
Need Help
Show and Tell
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
viralpatel21
Icon for Helper II rankHelper II
3 years ago

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".

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      viralpatel21
      Icon for Helper II rankHelper II
      3 years ago

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