Repeated rows

[Anonymous]

Hi all,

I am trying to get data from 2 different tables using table visualisation. Can I know why my rows is being repeated over and over again? I have some rows that just behave as "headers" to other rows from Excel and other columns empty, I have no idea why the value of one of the column I get from a different file gets repeated over and over again throughout the rows.

[AntrikshSharma]

@Anonymous  Is there an active relationship between these 2 columns or a relationship through a fact table? Can you share the file? 

FYI, when you bring columns from 2 dimensions PBI has to create a CROSSJOIN of two columns, when you drag a measure that removes filters from both the columns then what you get is combination of all the values of both columns 

[Anonymous]

@AntrikshSharma 

Yes there is. What I am trying to do is pretty complicated and I am trying to solve it one issue at a time.

This is my relationship table ( i am aware of the many-to-many relationship which is not ideal but everything gets messed up when I did it another way)

[AntrikshSharma]

@Anonymous  It is good that you know this is a bad model, at least I won't be able to provide a solution in case of M:M without interacting with the PBI file. Try to mask the data and share the file.

[Anonymous]

@AntrikshSharma 

Best I can do is,

 

S/N and Designation and Cable Number and length is from cable schedule, size is from datasheet Tag is from bridge Table and RunAmp is fromloadlist. I can't seem to solve this issue

Appreciate your help.

[v-shex-msft]

Hi @Anonymous,

How did your bridge table design? I'd like to suggest you pick up two side table fields values and remove duplicate values to use it link two side tables with 'many: one' relationship and it may be fixed the record mapping issue.

Bridge =
DISTINCT (
    UNION ( VALUES ( 'Cable Schedule'[Column] ), VALUES ( 'loadlist'[Column] ) )
)

Regards,

Xiaoxin Sheng

[Anonymous]

@AntrikshSharma 

I cant seem to mask the items as I need all of them to match and show accordingly so i will need to change them manually is there any other way for me to do it? Or any other possible reason for this to happen?

Thanks.

[amitchandak]

@Anonymous ,

new column =
countx(filter(Table, [ID] = earlier([ID]))[ID]) // greater than 1 you are looking for

Put with ID , any ID was displyed is repeating

New measure =
countx(filter(Summarize(Table, Table[ID], "_1" count(Table[ID])),[_1]>1),[ID])

[Anonymous]

@amitchandak 

Apologies, so I should add another column to remove the duplicates? Will it affect my other rows?

[Sumanth_23]

hi @Anonymous  - can you please include the headers in the screen grab for a little more clarity.

Also can you confirm that column you do not want to repeat is not of numeric type. Thanks! 

Please mark the post as a solution if my comment helped with solving your issue. Thanks! 

[Anonymous]

@Sumanth_23 

The Headers are

S/N

Cable Name

Type

ID

Length

Ampere

The column is a numeric type as I did some formulas and calculations on it. Do I have to change it?

[Sumanth_23]

hi @Anonymous - I wanted to confirm what you are trying to do here? Is the expectation that you want to see all highlighted in BLUE box (e.g. 92) as 1 row and see sum of all highlighted in the RED box in that row? 

[Anonymous]

@Sumanth_23 

Nope, the items is blue has no link to anything at all, hence it should be blank but I do want the items in the red box to be shown as one row.

[Sumanth_23]

@Anonymous - Sorry could be cause of the many to many relationship between the table - not able to figure out with seeing a sample file with the data 😐

[amitchandak]

@Anonymous , based on key on which yow want to check repetition

new column =
countx(filter(Table, [ID] = earlier([ID]))[ID]) // greater than 1 you are looking for

// ID and S/N

new column =
countx(filter(Table, [ID] = earlier([ID]) && [S/N] = earlier([S/N]))[ID]) // greater than 1 you are looking for

New measure =
countx(filter(Summarize(Table, Table[ID], "_1" count(Table[ID])),[_1]>1),[ID])

// ID and S/N

New measure =
countx(filter(Summarize(Table, Table[ID], Table[S/N], "_1" count(Table[ID])),[_1]>1),[ID])

[amitchandak]

@Anonymous , You want to delete. I thought you want to check duplicate. For that Edit Query is a better place.

refer these two, if they can help

https://stackoverflow.com/questions/51798712/remove-duplicates-values-based-on-multiple-column-with-a-condition-in-query-edit

https://www.youtube.com/watch?v=rqDdnNxSgHQ

You need similar column in M now to delete