Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Get Value corresponding to max date over Primary Key

Hello Team,   I am fairly new to Power BI and I am stuck with a logic where i have to Get the value from a column based on max date of each request.   My Tables consist of a Request table connect...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create two measures in comments table,

    Max date = CALCULATE(MAX('Comments table'[date]),ALLEXCEPT('Comments table','Comments table'[ID]))

flag = IF(MAX('Comments table'[date])=[Max date],1,0)

    Add [flag] in the visual level filter

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

Does two tables look like this?

Request table

ID
1
2
3

 

Comments table

ID date comment
1 5/1/2019 comment11
1 5/2/2019 comment12
1 5/3/2019 comment13
2 5/1/2019 comment21
2 5/2/2019 comment22
2 5/3/2019 comment23
3 5/1/2019 comment31
3 5/2/2019 comment32
3 5/3/2019 comment33

 

Request table (relationship: one to many) Comments table

 

Finally, the output should be as follows:

Get the value from a column based on max date of each request

ID date comment
1 5/3/2019 comment13
2 5/3/2019 comment23
3 5/3/2019 comment33

 

Is my understanding correct?

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-juanli-msft ,

     

    Thank you for helping me out. Yes this is how the output should be.

     

    Thank you,

    Siddhesh H Mane.

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous 

      Create two measures in comments table,

      Max date = CALCULATE(MAX('Comments table'[date]),ALLEXCEPT('Comments table','Comments table'[ID]))

flag = IF(MAX('Comments table'[date])=[Max date],1,0)

      Add [flag] in the visual level filter

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-juanli-msft ,

         

        Thanks a lot, will definitely try this one out. I had tried using the below DAX to display the output for the time being

         

        Latest Comments = MAXX(FILTER(RequestComments, RequestComments[RequestId] && RequestComments[Created] = MAX(RequestComments[Created])), RequestComments[Comments])
         
        I don't know exactly if this is the right approach, but i was getting the desired output.
        Please let me know if this is correct.
         
        Thanks a lot,
        Siddhesh H Mane.