Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
s4spencer
Regular Visitor

Table visualization - "hide" columns when summarizing

Hi,

 

I'm trying to "summarize - last" based on a specific column, for example:

 

Index | Letter | Date

0 | A | 22/12

1 | B | 22/12

2 | A | 23/12

3 | B | 23/12

 

I want to "summarize - last" the "Date" column, based on the "Letter" column. However since "Index" is unique, all rows still show up in the table.

Is there a way for me to keep "Index" visible, but "hide" it when the Table is summarizing by date?

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @s4spencer ,

 

First create a dimensional date table as below:

Date table = VALUES('Table'[ Date])

Then create a measure as below:

_Index = 
var _table=CALCULATETABLE(VALUES('Table'[Index ]),FILTER(ALL('Table'),'Table'[ Date] in FILTERS('Table'[ Date])))
Return
IF(NOT(ISFILTERED('Date table'[ Date])),MAX('Table'[Index ]),
IF(MAX('Table'[Index ]) in _table,MAX('Table'[Index ]),BLANK()))

Remember to create a relationship between fact table and date table:

Screenshot 2020-11-16 134831.png

Finally you will see:

Screenshot 2020-11-16 135020.pngScreenshot 2020-11-16 135030.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi @s4spencer ,

 

First create a dimensional date table as below:

Date table = VALUES('Table'[ Date])

Then create a measure as below:

_Index = 
var _table=CALCULATETABLE(VALUES('Table'[Index ]),FILTER(ALL('Table'),'Table'[ Date] in FILTERS('Table'[ Date])))
Return
IF(NOT(ISFILTERED('Date table'[ Date])),MAX('Table'[Index ]),
IF(MAX('Table'[Index ]) in _table,MAX('Table'[Index ]),BLANK()))

Remember to create a relationship between fact table and date table:

Screenshot 2020-11-16 134831.png

Finally you will see:

Screenshot 2020-11-16 135020.pngScreenshot 2020-11-16 135030.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

s4spencer
Regular Visitor

To expand on this, I am aware of using calculated columns for this, like:
https://community.powerbi.com/t5/Desktop/How-to-find-latest-date-value-in-a-group/td-p/379104

Just hoping there's a cleaner way

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.