Forum Discussion

PBI_learn_NK's avatar
PBI_learn_NK
Frequent Visitor
2 years ago
Solved

How to Display Rows Without Aggregation in Table Visual?

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:...
Need Help
  • PBI_learn_NK's avatar
    PBI_learn_NK
    2 years ago

    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.

PBI_learn_NK's avatar
PBI_learn_NK
Frequent Visitor
2 years ago

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's avatar
Irwan
Super User
2 years ago

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's avatar
    PBI_learn_NK
    Frequent Visitor
    2 years ago

    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's avatar
      Irwan
      Super User
      2 years ago

      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's avatar
        PBI_learn_NK
        Frequent Visitor
        2 years ago

        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.