Forum Discussion

helen_brunyee95's avatar
helen_brunyee95
Icon for Advocate I rankAdvocate I
2 years ago
Solved

LOOKUPVALUE with multiple conditions in one table

Hello Everyone,

 

I have been trying to work this out for several days now and can't quite get it.

 

I have a "audit" table which records/logs the status of users each month (so essentially, every month it looks at all the User IDs and makes a note/takes a snapshot of the status is was that month, along with the date (last day of the month)).  Example of the table:

UserIDStatusSnapshotDateIsInLastFY?Status Prev FY (end)
D1234

ACT

31/03/2024YACT
D1234DIS29/02/2024NACT
D5678DIS30/09/2022NDIS
D5678ACT31/12/2023YDIS
D5678DIS31/03/2024YDIS
D1234ACT31/05/2024NACT

 

What I am trying to do is work out what the status was for that user the last month of the previous financial year, for e.g. when looking at the report in June 2024, this column would show the status for that user in March 2023. (It needs to be as a column in the table due to how I am going on to use this field)

 

I have managed it when looking back previous months, using columns such as:

 

 

The issue I am having is having seems to come from me wanting to lookup that status, using the userId AND the date being in last FY (using my FY flag column) AND it being the last record from the last FY.

 

Hopefully that all makes sense!

 

Thank you so much in advance, any help is greatly appreciated! 🙂

 

Best,

Helen

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi helen_brunyee95 ,

    Ok, please try this calculated column syntax:

    _Status Prev FY (end) = 
VAR _currentID = 'Table'[UserID]
VAR _vtable =
    SELECTCOLUMNS(FILTER (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[IsInLastFY?] = "Y" ),
            "_MaxDate",
                CALCULATE (
                    MAX ( 'Table'[SnapshotDate] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                    )
                )
        ),
        'Table'[SnapshotDate] = [_MaxDate]
    ),"_ID",'Table'[UserID],"__Status",'Table'[Status])
RETURN
    CONCATENATEX (
        FILTER ( _vtable, [_ID] = _currentID ),
        [__Status]
    )

     

    The final page visual effect is as follows:

     

    If you have any other questions please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi helen_brunyee95 ,

     

    Thanks for the reply from MFelix .

     

    I use your sample data and create a measure:

    Status Prev FY (end) =
VAR _vtable =
    FILTER (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[IsInLastFY?] = "Y" ),
            "_MaxDate",
                CALCULATE (
                    MAX ( 'Table'[SnapshotDate] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                    )
                )
        ),
        'Table'[SnapshotDate] = [_MaxDate]
    )
RETURN
    CONCATENATEX (
        FILTER ( _vtable, [UserID] = SELECTEDVALUE ( 'Table'[UserID] ) ),
        [Status]
    )

     

    Now you can realize your red column.

     

    The final page visual effect is as follows:

     

    If you have any other questions please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • helen_brunyee95's avatar
      helen_brunyee95
      Icon for Advocate I rankAdvocate I
      2 years ago

      Hi Anonymous 

       

      Thank you so much for getting backt o me and working this out - it is great & does what I am trying to calculate.

       

      Is there a way to get this as a column though? As a column, I will be able to then count the number of each status in a card visual, for example. As a measure, I wouldn't be able to do that.

       

      Thanks again for all your help, I really apprecaite it.

       

      Best,

      Helen

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi helen_brunyee95 ,

        Ok, please try this calculated column syntax:

        _Status Prev FY (end) = 
VAR _currentID = 'Table'[UserID]
VAR _vtable =
    SELECTCOLUMNS(FILTER (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[IsInLastFY?] = "Y" ),
            "_MaxDate",
                CALCULATE (
                    MAX ( 'Table'[SnapshotDate] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                    )
                )
        ),
        'Table'[SnapshotDate] = [_MaxDate]
    ),"_ID",'Table'[UserID],"__Status",'Table'[Status])
RETURN
    CONCATENATEX (
        FILTER ( _vtable, [_ID] = _currentID ),
        [__Status]
    )

         

        The final page visual effect is as follows:

         

        If you have any other questions please feel free to contact me.

         

        The pbix file is attached.

         

        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

         

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    Hi helen_brunyee95 ,

     

    Taking you example into account the information believe is not matching you refer that it should pick up the last financial year information in your example is June 2024 correspond to March 2023 how do we know what is the period we should look at?

     

    On second place also looking at the data you gave how is final column getting values? Again the dates for me are not making any sense.

    I have sorted the information by user and date and not abble to understand how the status is ACT or DIS for each user.