Highlighting groups of duplicates

[mwjansen]

Hello all. 

I'd like some way to dilineate the groups of duplicates. Could be formatting, spacing, a line ... Is there a way? 

[ToddChitt · Accepted solution]

There must be some other column that you wish to include in this visual, yes? A quick and easy way to do that is to make it a Matrix visual instead of a table. But the Name field on the Rows. Then add some other field below that. That will GROUP all the same Names together. It won't repeat the values on every row, but it communicates the same thing.

[mwjansen]

Follow up question. In a matrix, I want it to show names down the first column, classes across the rows, and the date (expiration date) in the matrix. 

I've put the date in the 'values' field of the matrix visual, which turns to 'Earliest Date' (I could choose 'latest' or 'count'). However, any class that doesn't have an expiration date, does not show up. The entire class column is removed. ie 'Eye Protection' cannot be seen there. In the table, these cells are blank. I've set the filter to all, but I think because it defaults to 'earliest date', these blank entries aren't counted. I've tried different measures with zero success. How can I can get them to show, or to have that just be 'date' and not 'earliest date'? 

[v-hjannapu]

Hi @mwjansen,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to @kushanNa, @danextian , @ToddChitt   for those inputs on this thread. 

Use a Matrix Visual If you change your table to a Matrix, and add Name to the Rows, and then include your other fields like Date, Module, etc. below it, Power BI will automatically group the same names together. This way, the name appears only once, and the related rows are neatly grouped under it.
Use Conditional Formatting If you want to stick with a Table visual, you can add some simple conditional formatting to highlight each name group. For example, you can create a measure that checks for duplicates and use it to apply background color to make each block stand out. This doesn’t add spacing, but helps the eye see where each group starts and ends.
please go through with the below document hope it may resolve your Issue:
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-matrix-visual?tabs=powerbi...
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Harshitha.

[kushanNa · Accepted solution]

Hi @mwjansen 

Are you looking for an output similar to this?

If so, the method I followed here is:

First, I created a Grouping ID calculated column for duplicates:

GroupID = 
VAR CurrentValue = 'YourTable'[Item]
RETURN
RANKX(
    FILTER(
        DISTINCT('YourTable'[Item]),
        NOT ISBLANK('YourTable'[Item])
    ),
    'YourTable'[Item],
    ,
    ASC,
    DENSE
)

Next, I created a GroupColor calculated column:

GroupColor = 
VAR GroupID = 'YourTable'[GroupID]
VAR Red = MOD(GroupID * 89, 256)
VAR Green = MOD(GroupID * 163, 256)
VAR Blue = MOD(GroupID * 37, 256)

-- Helper function to convert a number 0-255 into a 2-digit hex string
VAR HexChars = "0123456789ABCDEF"

VAR RedHi = INT(Red / 16)
VAR RedLo = MOD(Red, 16)

VAR GreenHi = INT(Green / 16)
VAR GreenLo = MOD(Green, 16)

VAR BlueHi = INT(Blue / 16)
VAR BlueLo = MOD(Blue, 16)

RETURN
"#" &
MID(HexChars, RedHi + 1, 1) &
MID(HexChars, RedLo + 1, 1) &
MID(HexChars, GreenHi + 1, 1) &
MID(HexChars, GreenLo + 1, 1) &
MID(HexChars, BlueHi + 1, 1) &
MID(HexChars, BlueLo + 1, 1)

Then I used that column for background conditional formatting.

I've attached the demo I created for your reference.

[mwjansen]

@kushanNa 

Fantastic! while it won't work well for this particular application, I have others where it fits perfectly. Thanks ever so much! 

[danextian]

Wel, you can create a measure to be used as a field value for conditional formatting

IF (
    CALCULATE ( COUNTROWS ( 'table' ), ALLEXCEPT ( 'table', 'table'[name] ) ) > 1,
    "yellow"
)

[ToddChitt · Accepted solution]

There must be some other column that you wish to include in this visual, yes? A quick and easy way to do that is to make it a Matrix visual instead of a table. But the Name field on the Rows. Then add some other field below that. That will GROUP all the same Names together. It won't repeat the values on every row, but it communicates the same thing.

[mwjansen]

@ToddChitt 

Yes, there are other columns. It is essentially a training matrix, so other columns would be date, module, expiry, etc. I don't necessarily need delineation across all the rows, though that would be better aesthetic.