Replace repeating row values with blank in a Table Visual

[scd66]

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.

[v-echaithra]

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.

[scd66]

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

[v-echaithra]

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.

[scd66]

Could you perhaps show a screenshot showing how the DAX measures look in your sample file in the formula bar and I could try copying?

[scd66]

Thanks but your attachment won't open on my laptop. It produces the following message:

[v-echaithra]

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.

[v-echaithra · Accepted solution]

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

[v-echaithra]

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.

[v-echaithra]

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.

[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:

[scd66]

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:

[v-echaithra]

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-...

[scd66]

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'.

PBI Table Visual Format.xlsx

[danextian]

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?

[scd66]

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:

PBI Table Visual Format.xlsx

[Ashish_Mathur]

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.

[scd66]

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:

PBI Table Visual Format.xlsx

[Ashish_Mathur]

I am unable to understand your requirement.  Someone who does will help you.