Forum Discussion

Agustina's avatar
Agustina
Frequent Visitor
5 years ago
Solved

Conditional column from Many to One

Hello colleagues,

 

I have these 2 tables:

2021 Cases - unique records (clients)

2021 Online Q - each client has multiple rows, one per topic, with a status (responsecasestatus) and a date.

 

 

I want to create a calculated column on the 2021 Cases that would bring me the Last Modified date for that client, once there are no rows-Topic on status "Waiting for Corrections". It's crucial that all records for that client have other status.

 

The Last Modified date can be different for the same client, as it varies by Topic. I would want to flag the most recent one.

 

The 2021 Online Q table looks like this:

 

So we have different topics for each participant ID, each topic can have the status:

Waiting for Corrections

Waiting for Review

Review in Progress

 

I want to bring a calculated column on the clients table, with the last modified date per client id, only once I have no other rows on Waiting for Corrections.

So for example client ALB378273 will show blank as we still have one topic waiting for corrections, but client ARE379242 is all good to go and will need to show the latest date which is March 12th.

 

Hope this explained a little bit more what I need to do and that you can show me some light here

Regards,

Agustina

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Agustina ,

     

    try the following measure:

    LastDate =
IF(
    CALCULATE(
        COUNTROWS( '2021 Online Q' ),
        '2021 Online Q'[ResponseCaseStatus] = "Waiting for Corrections"
    ) > 0,
    BLANK(),
    CALCULATE( MAX( '2021 Online Q'[LastModified] ) )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hello Agustina ,

     

    I didn't understand the issue with the "status 'Waiting for Corrections'". Maybe you can explain that again?

    Except for that you can add a new calculated column to the table '2021 Cases':

    LastDate = CALCULATE( MAX( '2021 Online Q'[LastModified] ) )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Agustina's avatar
      Agustina
      Frequent Visitor
      5 years ago

      Hi selimovd I've just updated the original post with more information to explain it better 🙂 thank you!

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey Agustina ,

         

        try the following measure:

        LastDate =
IF(
    CALCULATE(
        COUNTROWS( '2021 Online Q' ),
        '2021 Online Q'[ResponseCaseStatus] = "Waiting for Corrections"
    ) > 0,
    BLANK(),
    CALCULATE( MAX( '2021 Online Q'[LastModified] ) )
)

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis