Matrix: Add one measure to rows and others to values

[E__]

Hi,

I am trying to achieve the following: a matrix containing product categories and products, with the number of times it has been used per year, and the last date it was used. I managed to get the first part, by adding the year to the columns, and a count to the values, but I can't get the last date in. I can get the last date per year, but I want the last date overall. I tried to drag my measure into the rows, but wasn't succesfull. Is there a way to manage this using a matrix? I don't want to use a table, because then I'd have to make a measure for each year, and new years wouldn't automatically be added.

Thanks in advance!

Best,

Eva

[Greg_Deckler]

That should be something like:

MAXX(ALL('Table'),[Date])

[E__]

Hi @Greg_Deckler @MFelix 

I was able to define the measure, but the problem is, when I drag the measure into the values in my matrix, it will show the correct date multiple times, once for each year (see screenshot of a part of my matrix below).

Is there a way to add the date only once? 

Best,

Eva

[Greg_Deckler · Accepted solution]

Yeah, that can be annoying. One method for fixing this is to turn off word wrap and then shrink the unwanted columns to nothing. There may be some other options as well such as formatting the matrix a little differently where you put your years in rows and turn off the Stepped layout.

[E__]

@Greg_Deckler  Thanks, shrinking the unwanted columns is a good workaround! It's a shame that there's no existing functionality for this, as I can't imagine I'm the only one wanting to combine measures per year with total measures in a matrix...

[Greg_Deckler]

It's come up before! I created a kind of work-a-round but it is a pretty complex thing. I'll attach the PBIX though in case it suits your needs or you find it a better way of doing it. Essentially, it is sort of an implementation of the Disconnected Table Trick with a bit of a twist.

[MFelix]

Hi @E__ ,

You need to create a measure similar to this one:

Last date = CALCULATE(MAX(Table[Date]); ALL(Table)

Be aware that this is just an overall example this may need to be adjusted to your model, without any data is difficult to guide you better.

With this measure you should get the same values on all the years.