Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to treat Duplicate rows as Unique Rows

Hi,

 

Greetings.

 

I have just started playing around with power BI and relatively new to the analytics space. There is a problem which I am trying to resolve from past two days.

I have imported an excel spreadsheet which has four columns - Video content, Plays, Finishes and duration of the video. I just want to make sure that the videos are listed on the x-axis and the plays and finishes on the Y-axis. The problem is that there are few videos with similar names, so the data is getting appended and reflected in my clustered column chart. I've referred to a previous post where it was suggested that we add the ID(unique column) to the query, but the solution didn't help me out.

So my question to the group is without any uniqueIDs or identifier, how can I make powerBI consider the duplicate rows as unique records and list them in X-axis with plays and finishes in Y-axis.

 

Any leads would be greatly appreciated.

 

Thanks & regards,

A Newbie seeking expert's advice.

Need Help
Tips and Tricks
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Adding the index in the query  editor is a good first step. 

     

    You can then add a column that counts the number of occurrences of a video name and increments each time if finds another copy of the name.

     

    Occurrence = 
CALCULATE (
    COUNTA ( Data[video] ),
    FILTER (
        Data,
        Data[video] = EARLIER ( Data[video] )
            && Data[Index] <= EARLIER ( Data[Index] )
    )
)

     

     

    Then you add a column for the unique video name that used the Occurrence which will leave the video name alone on the first occurence but add the occurance number on any subsequent matches.

     

    video unique = IF ( Data[Occurrence] = 1 , Data[video] , Data[video] &" (" & Data[Occurrence] & ")" )

     

    Then just use the Video Unique in your chart.

     

     

     

     

     

     

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Adding the index in the query  editor is a good first step. 

     

    You can then add a column that counts the number of occurrences of a video name and increments each time if finds another copy of the name.

     

    Occurrence = 
CALCULATE (
    COUNTA ( Data[video] ),
    FILTER (
        Data,
        Data[video] = EARLIER ( Data[video] )
            && Data[Index] <= EARLIER ( Data[Index] )
    )
)

     

     

    Then you add a column for the unique video name that used the Occurrence which will leave the video name alone on the first occurence but add the occurance number on any subsequent matches.

     

    video unique = IF ( Data[Occurrence] = 1 , Data[video] , Data[video] &" (" & Data[Occurrence] & ")" )

     

    Then just use the Video Unique in your chart.

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello There,

       

      Thank you for your response. Really appreciate your help.

      I did try implement your solution by trying to input the syntax in the advance query editor but the app is just not letting me execute it.

      Apologies again, I am a newbie without any knowledge of using the editor and stuff.

       

      Thanks for your help again.

       

       

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Anonymous

         

        The Occurance and Video Unique are calculated columns in DAX, not in the query editor.  Once you have the index column hit 'Close & Load' in the top left. You are done in the query editor.

         

        Then you go to the table in the data view and click Add Column and put in the formula for Occurrence.

        Then click Add Column again and put in the formula for Video Unique