Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Latest Record Based on Date Slicer

First time question poster here!   Excel and PowerBi files here: https://www.dropbox.com/sh/g3pfws2xzkmqgqy/AADUrdKWRzHDSCQ5qF8gVQxDa?dl=0  I have change log of many aspects for employees, and I...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Check if it is correct on your side.

    Create a new table

    attribute = VALUES('Log'[Attribute])

    Modify measure as below

    attribute selected =
VAR maxdate =
    CALCULATE (
        MAX ( 'Log'[Record Date] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[In View] = 1
        )
    )
VAR attr =
    CALCULATE (
        MAX ( 'Log'[Attribute] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[Record Date] = maxdate
        )
    )
RETURN
    IF (
        HASONEFILTER ( attribute[Attribute] ),
        IF (
            SELECTEDVALUE ( attribute[Attribute] ) = attr,
            attr
        ),
        attr
    )

     

    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
6 years ago

Hi Anonymous 

Check the pbix file.

last index in view = 
VAR maxdate =
    CALCULATE (
        MAX ('Log'[Record Date] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[In View]
                    = 1
        )
    )
RETURN
    CALCULATE (
        MAX ('Log'[Index] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ('Log'[Employee ID] )
                && 'Log'[Record Date] = maxdate
        )
    )

attribute selected = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Log'[Record Date] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[In View]
                    = 1
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Log'[Attribute] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[Record Date] = maxdate
        )
    )

 

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
6 years ago

Hey Maggie,

Thanks again for attempting again and correcting the syntax. I am however getting the same results as my measure. Screenshots are below. The issue is that when applying a slicer for attribute 1, it is returning all the employees that at one point have had a 1 in the table, not just the employees that have a 1 on the date selected in the slicer.

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

    Hi Anonymous 

    Check if it is correct on your side.

    Create a new table

    attribute = VALUES('Log'[Attribute])

    Modify measure as below

    attribute selected =
VAR maxdate =
    CALCULATE (
        MAX ( 'Log'[Record Date] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[In View] = 1
        )
    )
VAR attr =
    CALCULATE (
        MAX ( 'Log'[Attribute] ),
        FILTER (
            ALLSELECTED ( 'Log' ),
            'Log'[Employee ID]
                = MAX ( 'Log'[Employee ID] )
                && 'Log'[Record Date] = maxdate
        )
    )
RETURN
    IF (
        HASONEFILTER ( attribute[Attribute] ),
        IF (
            SELECTEDVALUE ( attribute[Attribute] ) = attr,
            attr
        ),
        attr
    )

     

    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.