Forum Discussion
Latest date in a matrix
Hello,
I have test data like this:
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 2:
I have tried with RANKX function in a measure but none of my attempts worked.
Any suggestion is appreciated!
Hi, nenadpekec
Please check the below picture and the sample pbix file's link down below.
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
5 Replies
- Jihwan_Kim
Super User
Hi, nenadpekec
Please check the below picture and the sample pbix file's link down below.
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
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!- Jihwan_Kim
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.
Thank you for the solution..
In my case your solution is working but it is taking more time for the visual to load.Shall we do the same thing in a form of calculated column instead of a measure.
- amitchandak
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 )
