Forum Discussion

nenadpekec's avatar
nenadpekec
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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

nenadpekec's avatar
nenadpekec
Frequent Visitor
5 years ago

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's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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.