Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using a slicer to compare two rows in the same table

I have a series of records with unique IDs, and I'm creating monthly snapshots of these records with a generic text description field in place. Something like:

 

RecordDateComments
AMarch 1, 2021Just started
AJuly 3, 2021All is well
ASeptember 20, 2021Something changed
BJuly 3, 2021All is well
BSeptember 20, 2021All is well

 

So, I'd like to user to pick a reference date (I've been trying via slicer). E.g., July 3 as above. Then at the last record (September 20 in the example) I'd like to go back and see which records changed. In this case, I'd like to identify Record A as having changed, and nothing for Record B. If I went back and selected March 1 as my reference (in the above example), I'd still like to identify Record A and skip Record B.

 

Any suggestions as to how to manage this so a user could dynamically pick a reference date and then see which records have had comments changed? Thanks!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous

     

    Thank you for the explanation. Based on the sample data, you can create below measure.

    Current Comment = 
VAR maxDate = CALCULATE(MAX(Record_Table[Date]),ALL(Record_Table))
VAR currentComment = CALCULATE(MAX(Record_Table[Comments]),ALLEXCEPT(Record_Table,Record_Table[Record]),Record_Table[Date]=maxDate)
VAR initialComment = SELECTEDVALUE(Record_Table[Comments])
RETURN
IF(currentComment = initialComment,BLANK(),currentComment)

     

    Put Date column into a slicer. Put Record, Comments and above measure into a table visual. Comments column will show comments on a selected initial date. You can rename Comments column for this table visual specificially. 

    Let me know if you have any questions.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    4 years ago

    what is your expected output?  How are you expecting this to look?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I need to be able to display a list of all the "changed" records and display how they have changed. So a later table view would identify Record A, its user-selected "initial" state (comment), and the current comment.

       

      I'm starting with thousands of records and this process should filter the list down to the tens/hundreds that show activity and should be highlighted.

       

      My thought was that I would want to add a column to my initial table that would record the user-selected "initial" comment so it would be easy to tag a difference. The closest I got was the following (simplified to match with the example above): 

       

      Start Comment =
      LOOKUPVALUE(Record_Table[Comments],Record_Table[Snap Date],"July 3, 2021",
      Record_Table[Record],Record_Table[Record])
       
      This works until I try to replace "July 3, 2021" with a measure.
      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        I have two questions, can you help me understand them better? Currently in your sample table, the latest date for both records is September 20, 2021. Is it possible that different records have different latest record date? And when a user selected an initial date, is it possible that a record doesn't have a record on that day but it has one before that day, so we need to count the latest record earlier than or equal to the initial day?

         

        Best Regards,
        Community Support Team _ Jing