cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nenadpekec
Frequent Visitor

Latest date in a matrix

Hello,
I have test data like this:
Test data.png

NOTE: You can download it at https://gofile.io/d/xPHIGM

 

The report should be simple, one slicer (Date) and a Matrix visual.
The problem that I'm unable to solve is to display the latest date in a matrix (column), based on user’s selection in the slicer.


In the examples below, I need to display only dates underlined with green line (the latest date per user, based on selection in the slicer).

Example 1:

Example 1.png

Example 2:

Example 2.png

I have tried with RANKX function in a measure but none of my attempts worked.

Any suggestion is appreciated!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @nenadpekec 

Please check the below picture and the sample pbix file's link down below.

 

Picture11.png

 

Value Total =
VAR currentuser =
MAX ( Users[User] )
VAR currentparameter =
MAX ( Parameters[Parameters] )
VAR latestdate =
CALCULATE (
MAX ( 'Values'[Date] ),
FILTER (
ALLSELECTED ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
)
)
RETURN
CALCULATE (
SUM ( 'Values'[Value] ),
KEEPFILTERS (
FILTER (
ALL ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
&& 'Values'[Date] = latestdate
)
)
)

 

 

https://www.dropbox.com/s/ufn9epk67zsk9qe/nenadpekec.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @nenadpekec 

Please check the below picture and the sample pbix file's link down below.

 

Picture11.png

 

Value Total =
VAR currentuser =
MAX ( Users[User] )
VAR currentparameter =
MAX ( Parameters[Parameters] )
VAR latestdate =
CALCULATE (
MAX ( 'Values'[Date] ),
FILTER (
ALLSELECTED ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
)
)
RETURN
CALCULATE (
SUM ( 'Values'[Value] ),
KEEPFILTERS (
FILTER (
ALL ( 'Values' ),
'Values'[User] = currentuser
&& 'Values'[Parameters] = currentparameter
&& 'Values'[Date] = latestdate
)
)
)

 

 

https://www.dropbox.com/s/ufn9epk67zsk9qe/nenadpekec.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Thank you very much, Kim.

This worked as a charm!

Would you mind explaining what was the reason for splitting data into multiple tables?
P.S. I have updated the measure and tried using columns from the same table, and it worked!

Hi, @nenadpekec 

Thank you for your feedback.

 

It is just a practicing purpose for me. 🙂

I try to create a star schema data model, if possible, even it is a small sample dataset.

In order to get the result in a small sample dataset, it is not quite relevant ( actually, not efficient) to do it in my way. However, I always try and practice creating dimension tables - fact table with proper relationships. 

Thanks. 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User
Super User

@nenadpekec , assume you have a measure value, Try a new measure or include value calculation in last calculate

 

Measure =
VAR __id = MAX ('Table'[User] )
VAR __date = CALCULATE ( MAX('Table'[NUM] ), ALLSELECTED ('Table' ), 'Table'[User] = __id )
CALCULATE ( [value], VALUES ('Table'[User] ),'Table'[User] = __id,'Table'[NUM] = __date )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!