How to Display Rows Without Aggregation in Table Visual?

[PBI_learn_NK]

Hello , 

I am facing an issue with displaying data in a table visual. My data consists of multiple columns, and I want to show each row without summarizing the values. Here is an example of my data:

Column 1 Column 2 Column 3 Column 4 Column 5

Value A1	Value B1	Value C1	Value D1	10000
Value A1	Value B1	Value C1	Value D1	10001
Value A1	Value B1	Value C1	Value D1	10002
Value A1	Value B1	Value C1	Value D1	10003

However, in the table visual, I need to display the data as follows:

Column 1 Column 2 Column 3 Column 4 Column 5

Value A1	Value B1	Value C1	Value D1	10000
				10001
				10002
				10003

I've added the columns to the table visual, but the values are being summarized by default, and I can't see an option to display them without summarization. Here’s what I’ve tried so far:

1. Added all columns to the table visual.
2. Set the numeric column to "Don't Summarize".

Despite these steps, the data still shows only the columns without values or summarizes the numeric field. What steps should I follow to display each row of data without summarizing?

Thank you for your assistance!

[Irwan]

Hello @PBI_learn_NK 

is this result match to your need?

Hope this will help you.

Thank you.

[PBI_learn_NK]

Thank you, Irwan, for your suggestion. I have already applied the "Don't Summarize" option for the last numeric column, but my goal is to display the first four columns with unique values in a single row without repeating the same values across multiple rows.

Here’s an example of my desired output:

Currently, when I add all columns to the table visual and set the numeric column to "Don't Summarize," I still
see each row repeated with the same values in the first four columns.

Could you please advise on how to achieve this display where the first four columns show unique values in a single row without repetition?

Thank you for your assistance.

[Irwan]

Hello @PBI_learn_NK 

not sure if this is the most efficient way, but here is the easiest way that i can think.

Also I added some value in your table just incase you need to differentiate value in those column (1-4).

Since you want to put value in all column at the lowest value of Column5, then you need conditional if to make them blank. Tabel visual will show all value in your column and matrix visual seems not match to your need.

Column 1 =

var _Value = 'Table'[Column1]

var _LowestValue =

MINX(

    FILTER(

        'Table',

        'Table'[Column1]=_Value

    ),

    'Table'[Column5]

)

Return

IF(

    'Table'[Column5]=_LowestValue,

    'Table'[Column1],

    ""

)

_Value is used for defining value in Column1

_LowestValue is used for looking the lowest value of Column5 with exact same value in Column1
Conditional if is used for making blank value for every same string in Column1 (the logic is if the value in Column5 is not matching to the lowest value of Column5 then it is blank).

Last step is do this for Column2, Column3, and Column4. Just copy from Column1 and change Column1 into working column (let say you are working on Column2 then change Column1 into Column2).

As I said, this might not be the most efficient way, other Power BI expert might have another idea how to achieve your need.

Hope this will help you.

Thank you.

[PBI_learn_NK]

@Irwan ,

Thank you,  for your detailed solution. Your approach works well for a few columns. However, since I have many more columns, I will try alternative approaches and keep you posted.

Thank you again for your assistance and creative solution!
Best Regards,
NK

[Irwan]

Hello @PBI_learn_NK 

if you have a lot of column then the only choice is merging them in Power Query first.

1. Select all column, then choose unpivot column

2. it will merge those column into one column

3. Create calculated column and use the previous DAX to make blank value

Duplicate =

var _Value = 'Table'[Value]

var _LowestValue =

MINX(

    FILTER(

        'Table',

        'Table'[Value]=_Value

    ),

    'Table'[Column5]

)

Return

IF(

    'Table'[Column5]=_LowestValue,

    'Table'[Value],

    ""

)

4. Plot using matrix visual

This way should accomodate with your multiple column data. However, the row seems not be able to be moved to the right side so the value of Column5 is on the left side. And this trick will need you to change your table structure (merging them into one column).

Hope this will help you.

Thank you.

[PBI_learn_NK · Accepted solution]

@Irwan , 

Thank you, Irwan, for your alternative solution. Your approach of unpivoting the columns in Power Query and then using DAX to make repeated values blank seems promising.  

1. Unpivoted the columns in Power Query to merge them into one column.
2. Created a calculated column using your provided DAX formula to conditionally blank out repeated values.
3. Plotted the data using a matrix visual.

This method accommodates my multiple column data and simplifies the process. However, as you mentioned, the row layout might need adjustment, but it’s a great start!

Thank you again for your assistance. I will try this approach and keep you posted on the results.