Remove Duplicates in GRID

[sabeensp]

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.

[MariaP · Accepted solution]

Hi @sabeensp ,

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

See 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

[Anonymous]

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

[sabeensp]

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

---

---

[Anonymous]

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. 

[sabeensp]

@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

[Ashish_Mathur]

Hi,

I don't think that is possible.

[sabeensp]

I think we should make a recommendation to MSFT.

---

@Ashish_Mathur wrote:

Hi,

I don't think that is possible.

---

---

---

[v-piga-msft · Accepted solution]

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. 

Hope this can help you!

Best  Regards,

Cherry

[MariaP · Accepted solution]

Hi @sabeensp ,

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

See 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

[Anonymous]

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.