Forum Discussion
Helper IReplace repeating row values with blank in a Table Visual
Hi scd66 ,
As per your data context i have reframed the measure, please try the below DAX measure, make sure you are using field names correctly and let me know if it works.
Capacity Supp =
IF ('Locations UDFs'[Room ID Code] = CALCULATE (MIN ( 'Locations UDFs'[Room ID Code] ),
FILTER ('Locations UDFs','Locations UDFs'[Room ID Code] = EARLIER ( 'Locations UDFs'[Room ID Code] ))),
'Locations UDFs'[Capacity],BLANK())
Regards,
Chaithra
Helper IHere is the Excel table version. I want the formula to replace the values in red with a blank
CURRENT FORMAT
| Building Name | Room No. | Room Description | Room ID Code (Unique ID Column) | NIA sqm | Room Function | Activity Tag | % Activity Tag | NIA Area Used sqm | Capacity | Unique | Location Notes | Functional Suitability |
| BUILDING NAME | 101 | MAIN ENTRANCE FOYER | 1010101 | 9.964 | WAITING AREA | AA | 100 | 4.982 | 1 | Unique | WAITING AREA | GRADE 2 |
| BUILDING NAME | 101 | MAIN ENTRANCE FOYER | 1010101 | 9.964 | WAITING AREA | B | 100 | 4.982 | 1 | Repeating row | WAITING AREA | GRADE 2 |
| BUILDING NAME | 105 | ACCESSIBLE TOILET & SHOWER | 1010105 | 5.406 | SHOWER AREA | R | 100 | 2.703 | 0 | Unique | ACCESSIBLE TOILET & SHOWER | GRADE 1 |
| BUILDING NAME | 105 | ACCESSIBLE TOILET & SHOWER | 1010105 | 5.406 | TOILETS (GENDER NEUTRAL) | B | 100 | 2.703 | 0 | Repeating row | ACCESSIBLE TOILET & SHOWER | GRADE 1 |
| 30.74 | 15.37 | 2 | ||||||||||
| PREFERRED FORMAT | ||||||||||||
| Building Name | Room No. | Room Description | Room ID Code (Unique ID Column) | NIA sqm | Room Function | Activity Tag | % Activity Tag | NIA Area Used sqm | Capacity | Unique | Location Notes | Functional Suitability |
| BUILDING NAME | 101 | MAIN ENTRANCE FOYER | 1010101 | 9.964 | WAITING AREA | AA | 100 | 4.982 | 1 | Unique | WAITING AREA | GRADE 2 |
| BUILDING NAME | 101 | 1010101 | WAITING AREA | B | 100 | 4.982 | Repeating row | GRADE 2 | ||||
| BUILDING NAME | 105 | ACCESSIBLE TOILET & SHOWER | 1010105 | 5.406 | SHOWER AREA | R | 100 | 2.703 | 0 | Unique | ACCESSIBLE TOILET & SHOWER | GRADE 1 |
| BUILDING NAME | 105 | 1010105 | TOILETS (GENDER NEUTRAL) | B | 100 | 2.703 | Repeating row | GRADE 1 | ||||
| 15.37 | 15.37 | 1 |
Community SupportHi scd66 ,
Thank you for providing the sample data.
Based on your description, you’re trying to suppress repeating values in a Power BI Table visual for certain columns, such that repeated values in subsequent rows are replaced with blanks for improved readability.
Try to use calculated columns that compare the current row to the first instance of that repeating group, based on the Room ID Code.
Create new calculated columns using the following DAX pattern for each column where you want to hide repeats.
Room Description Display =
IF (
'YourTableName'[Room ID Code (Unique ID Column)] =
CALCULATE (
MIN ( 'YourTableName'[Room ID Code (Unique ID Column)] ),
FILTER (
'YourTableName',
'YourTableName'[Room ID Code (Unique ID Column)] = EARLIER ( 'YourTableName'[Room ID Code (Unique ID Column)] )
)
),
'YourTableName'[Room Description],
BLANK()
)
and repeat this logic for any of the following columns you want to format this way for Room no. and NIA sqm.
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Regards,
Chaithra.
- scd66
Sorry I couldn't get your formula to work properly. I think I must have typed it incorrectly. It kept saying I had the syntax wrong. Here is a screenshot of the error message: