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
Community SupportHi scd66 ,
Sorry, I am not able to access the file which you have sent.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Regards,
Chaithra.
scd66I have shown the current format I am getting in PowerBI Table Visual below with the preferred format shown under that with selected repeating row values replaced with a blank. I have been advised that the DAX formula should be:
New column= if([Index] = minx(filter(Table, [Column1] = earlier([[Column])) , [index]) , [Column2], blank())
but I am struggling to apply this formula successfully with the column headers shown below. I'm really just looking for the formula above to be shown using the coulmn headers in the example shown below:
- scd66
Here 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 - v-echaithra
Hi 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: