Forum Discussion
Replace repeating row values with blank in a Table Visual
I have a table visual in PowerBI shown below where I have split total room areas into percentages of room owner where the room is shared. Is there a formula that could be written to replace selected repeating row values with a blank? I have a unique column called Room ID Code and want the repeating row to show the Capacity value only once in the first row, for example 'Room No.101 Main Entrance Foyer' shown in the screen shot below is split 50/50% between two owners and it shows a row for each 50% split, and repeats the Capacity value again in the next row. Is there an easy formula that could be written for the Capacity column which would replace the repeated Capacity row value with a blank so it only appears once and I can then have a running total for the Capacity column? Should I take a copy of the column and then apply the formula to that, as some of my calcuated columns for percentage splits of the room areas are referencing the total area of the room in each repeated row and I don't want to mess up the calculations as I also want to replace the repeating total room area duplicates with a blank? I tried using the matrix visual but it was becoming huge when I expanded it as I have so many columns in my visual.
Any advice would be greatly appreciated, thanks.
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
24 Replies
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Here is the format I have in my Table visual with the preferred result shown under it. The Room ID column is the unique identifer used to sort the data, but I don't know how to apply the 'Earlier' formula that replaces the repeating row with a blank if it equals the first unique row. The values I don't want to repeat are the 'NIA/NUA Room Areas' fields, the 'Capacity' field and the 'Location Notes' fields highlighted in yellow. The Excel file is attached:
I am unable to understand your requirement. Someone who does will help you.
For this to work, it's essential to identify which columns cause the room number to appear multiple times. The table must be sorted first by room number and then by those columns, as the formula checks whether the values in the current row match those in the previous row. Users should not change the sorting order; otherwise, blanks may appear not only after the first row but also unexpectedly within each room number group. Additionally, multiple tables can introduce further complexity. The key question is: Is this a want or a need?
Here is the format I have in my Table visual with the preferred result shown under it. The Room ID column is the unique identifer used to sort the data, but I don't know how to apply the 'Earlier' formula that replaces the repeating row with a blank if it equals the first unique row. The values I don't want to repeat are the 'NIA/NUA Room Areas' fields, the 'Capacity' field and the 'Location Notes' fields highlighted in yellow. The Excel file is attached:
Hi scd66 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...Hi, Here is a sample of the data in the link below showing the repeating rows and the required result shown below this. I can do this with Crystal Reports by suppressing duplicate row values if the value of the index column equals the earlier, or previous, row, but can't seem to find a similar formula in PowerBI which gives the same result. I think the 'EARLIER' command might work but not sure how to apply this in a formula or what the formula should look like. The aim is to show the Capacity column value only once so that I can have an accurate running total in the table visual, so I would like to replace the repeating values with a 'blank'.
Hi 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.I 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:
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 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.
Hi scd66 ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.Just to let you know, I eventually got the display I needed using PowerBI Report Builder which lets you use the 'Previous' Expression (similar to Crystal) to blank out the repeating values that you don't want to show in your report. I can now publish my reports on the PowerBI web platform and embed them in my web portal so that others can access the reports easily via a link. Thanks for all the help and advice last year.
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,
ChaithraWould the formula below work if all the columns were in one table called 'Merge1'?
Capacity Supp =
IF ('Merge1'[Bldg Loc ID] = CALCULATE (MIN ( 'Merge1'[Bldg Loc ID] ),
FILTER ('Merge1','Merge1'[Bldg Loc ID] = EARLIER ( 'Merge1'[Bldg Loc ID] ))),
'Merge1'[Capacity],BLANK())where Bldg Loc ID is the primary unique identifier key that links various tables together. The Capacity Column is in Merge1 which is a different table to the 'Locations UDFs' table where the Room ID Code column sits. I tried the original formula but it didn;t work as the capacity column is in the Merge1 table rather than Locations UDFs
It's o.k., I just tried it and it doesn't work and doesn't hide the repeating row value with a blank
Hi scd66 ,
If the provided answer was helpful, please mark it as "Accept as solution" and click "Yes" if you found it helpful, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Chaithra.HI scd66 ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Chaithra.Hi,
Sorry I tried your latest formula as a new column but it still said I had a syntax error and it wouldn't run.
Regards,
scd66
Hi scd66 ,
Please look into the pbix file attatched, where we tried to reproduce the scenario with your sample data.
Compare the Location Notes with Location Notes Display and Functional Suitability with Functional Display.You can try these below DAX measures to build calculated columns i.e, Location Notes Display and Functional Display.
For Index Column:Index =
RANKX(
ALL('Locations UDFs'),
'Locations UDFs'[Room ID Code]
& "|" & 'Locations UDFs'[Room Function]
& "|" & 'Locations UDFs'[Activity Tag]
& "|" & FORMAT('Locations UDFs'[% Activity Tag], "0.00"),
,
ASC,
DENSE
)
For Capacity Display Column:
Capacity Display =
VAR CurrentRoomID = 'Locations UDFs'[Room ID Code]
VAR MinRow =
CALCULATE (
MIN ( 'Locations UDFs'[Index] ),
FILTER ( 'Locations UDFs', 'Locations UDFs'[Room ID Code] = CurrentRoomID )
)
RETURN
IF ( 'Locations UDFs'[Index] = MinRow, 'Locations UDFs'[Capacity], BLANK() )For Location Notes Display:
Location Notes Display =
IF(
ISBLANK('Locations UDFs'[Capacity Display]),
BLANK(),
'Locations UDFs'[Location Notes]
)
For Functional Display column:Functional Display =
IF(
ISBLANK('Locations UDFs'[Capacity Display]),
BLANK(),
'Locations UDFs'[Functional Suitability]
)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.
