Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |