cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sabeensp
Helper IV
Helper IV

Remove Duplicates in GRID

Hello,

I have a very simple visual questions. I'm using Grid to show data. WHich has over 20 columns, but I only need to insert a break on first column and do not want to repeat the value.

 

2019-04-26_15-14-29.png

 

2 ACCEPTED SOLUTIONS
MariaP
Solution Supplier
Solution Supplier

Hi @sabeensp ,

 

Potential workaround is shown here if you are able to use the Table visual.

See last table for final  result requestedSee last table for final result requested

You need to add an index to your table and then add a column  with this code:

Previous Value = 
   
    var thisColumn1 =  'DataColumns'[column1]

    var thisIndex= 'DataColumns'[Index]

    var thisPrevIndex = CALCULATE(

                        MAX('DataColumns'[Index])

                        ,FILTER(ALL('DataColumns')

                            ,'DataColumns'[column1] = thisColumn1 && 'DataColumns'[Index] < thisIndex

                        )

    )

    var thePreviousValue = CALCULATE(

                                MAX('DataColumns'[column1])

                                ,FILTER(ALL('DataColumns')

                                    ,'DataColumns'[column1] = thisColumn1 && 'DataColumns'[Index] = thisPrevIndex

                                )

    )
 VAR  displayValue =   
                            IF ( 'DataColumns'[Index] <>  0   && thePreviousValue = thisColumn1   , BLANK(), thisColumn1 ) 

   return

      displayValue



 

Then create a measure to use the conditional fomatting for the column:

colorKPI = SWITCH( TRUE()
 , MAX (DataColumns[column1] ) = MAX (DataColumns[Previous Value]), "#000000", "#FFFFFF")

Finally , use both field fomratting and conditional formatting to format the colours accordingly.

I have uploaded the file also for you to see.

Note , when you sort by the other columns the order fo Column1 obiviously changes  and this may not be an acceptable solution for you, however it is a potential workaround.

 

SAMPLE ON DROPBOX

 

Thanks, 
Maria

 

 

 

View solution in original post

Hi @sabeensp ,

You could submit your idea in Power BI ideas Froum and add your comments there to improve Power BI and make this feature coming sooner.

Additional workaround for your requirement, you may could try Matrix in Power BI like below.

You could drag column1 and column2 in Row field and turn off the stepped layout of the Row headers. 

Capture.PNG

Hope this can help you!

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

What comes immediatley to mind is removing duplicates in the query editor under 'Remove Rows'. 

@AnonymousThat would affect all the report, I just need to remove from this visual. Is there a way?




 

Anonymous
Not applicable

Is the visual you shared what you want your end product to look like or is that what is currently being displayed? I am uncertain what exactly your end product should look like. 

@AnonymousEnd product shouodl like the one on the right, where COL1 values are not repeating. SO, if COL1 has value A 20 times, I would like to see A only once and COl2 values for each of other 19 rows to show

 

 

2019-04-26_15-14-29.png

 

 

Hi,

I don't think that is possible.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I think we should make a recommendation to MSFT.


@Ashish_Mathur wrote:

Hi,

I don't think that is possible.





 

Hi @sabeensp ,

You could submit your idea in Power BI ideas Froum and add your comments there to improve Power BI and make this feature coming sooner.

Additional workaround for your requirement, you may could try Matrix in Power BI like below.

You could drag column1 and column2 in Row field and turn off the stepped layout of the Row headers. 

Capture.PNG

Hope this can help you!

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

MariaP
Solution Supplier
Solution Supplier

Hi @sabeensp ,

 

Potential workaround is shown here if you are able to use the Table visual.

See last table for final  result requestedSee last table for final result requested

You need to add an index to your table and then add a column  with this code:

Previous Value = 
   
    var thisColumn1 =  'DataColumns'[column1]

    var thisIndex= 'DataColumns'[Index]

    var thisPrevIndex = CALCULATE(

                        MAX('DataColumns'[Index])

                        ,FILTER(ALL('DataColumns')

                            ,'DataColumns'[column1] = thisColumn1 && 'DataColumns'[Index] < thisIndex

                        )

    )

    var thePreviousValue = CALCULATE(

                                MAX('DataColumns'[column1])

                                ,FILTER(ALL('DataColumns')

                                    ,'DataColumns'[column1] = thisColumn1 && 'DataColumns'[Index] = thisPrevIndex

                                )

    )
 VAR  displayValue =   
                            IF ( 'DataColumns'[Index] <>  0   && thePreviousValue = thisColumn1   , BLANK(), thisColumn1 ) 

   return

      displayValue



 

Then create a measure to use the conditional fomatting for the column:

colorKPI = SWITCH( TRUE()
 , MAX (DataColumns[column1] ) = MAX (DataColumns[Previous Value]), "#000000", "#FFFFFF")

Finally , use both field fomratting and conditional formatting to format the colours accordingly.

I have uploaded the file also for you to see.

Note , when you sort by the other columns the order fo Column1 obiviously changes  and this may not be an acceptable solution for you, however it is a potential workaround.

 

SAMPLE ON DROPBOX

 

Thanks, 
Maria

 

 

 

View solution in original post

Anonymous
Not applicable

So I tried throwing your sample data into a matrix, turning off the stepped rows but the matrix is automatically removing the duplicates. I'll do some research and see if I can find a solution. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors