topic Re: Group a table visual values by a column that doesn´t show in the visual itself in DAX Commands and Tips

Group a table visual values by a column that doesn´t show in the visual itself

maneschr2022 — Thu, 28 Apr 2022 03:40:55 GMT

Hi all
I have a table as a visual on my dashboard and I´m using an ID column that makes the right grouping for the values that the table visual contain.

But I would like to be able to not show the ID column in the visual as doesn´t provide useful information for this purpose, so is there any sort of DAX that can help me to make sure to group the table visual by ID column but not adding it necessary on the visual itself?

If I just get rid of the ID column it displays less rows than it should, so definitely would like to make sure that the table visual is group by the ID column

Re: Group a table visual values by a column that doesn´t show in the visual itself

OwenAuger — Thu, 28 Apr 2022 05:25:46 GMT

Hi maneschr2022

Unfortunately Power BI Table and Matrix visuals don't have a direct equivalent of the "Show header" option in Tableau, which allows you to include but hide a column.

However here are two possible methods I've used in Power BI myself:

Include the ID column in the table, disable Text wrap for Values & Column Headers, and manually reduce the width of the ID column until the contents of the column can't be seen (it will still use a small amount of space).
This method only works if you are happy to disable Text wrap on the entire table.
Create a new column that appends an encoding of the ID to one of the columns you do want to display, using invisible characters.
You could use any characters from this Invisible Characters list. Encoding the ID using these characters could be tricky depending on the ID values.
For example, using DAX code to illustrate the idea, these would appear the same but be treated as distinct values in a visual:
"John" & UNICHAR ( 8203 )
"John" & REPT ( UNICHAR ( 8203 ), 2 )
"John" & REPT ( UNICHAR ( 8203 ), 2 ) & UNICHAR ( 8204 )

There could also be a custom visual out there that does this, but I'm not aware of one.

Regards,

Owen

Re: Group a table visual values by a column that doesn´t show in the visual itself

Jihwan_Kim — Thu, 28 Apr 2022 06:46:11 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I created a sample pbix file like below, and I hope these measures in this file can provide some ideas on how to apply to your data model.

Re: Group a table visual values by a column that doesn´t show in the visual itself

maneschr2022 — Thu, 28 Apr 2022 07:16:18 GMT

Hi Jihwan_Kim , is more related to the answer that Owen provided, but still hoping for another solution, at the moment I`m hiding the ID column from the table visual by just compressing the ID column header to the left to not be visible, but not ideal.

Re: Group a table visual values by a column that doesn´t show in the visual itself

Anonymous — Tue, 03 May 2022 08:00:21 GMT

Hi maneschr2022

You can try the second way that OwenAuger mentioned . Split the [ID] column into 2 columns, one for text and one for numbers . The premise of this split is that the text of your [ID] column must be consistent, and the numbers behind are different, so that you can group and merge according to the number column . I created a sample , you can have a look .

Original data :

Then split the [ID] column .

Go back to Desktop view , then create a measure to sum the value by column [ID.2] .

Measure = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[ID.2]))

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.