Forum Discussion

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

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.

Record Log Example3.pbix48 KB

v-juanli-msft

Community Support

6 years ago

Hi Anonymous

Create measures

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


attribute selected =
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Record Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Employee ID]
                = MAX ( 'Table'[Employee ID] )
                && 'Table'[In View]
                    = TRUE ()
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Attribute] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Employee ID]
                = MAX ( 'Table'[Employee ID] )
                && 'Table'[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

Not applicable

6 years ago

Hi Maggie,

Thanks for the response!

I have tried the solution but am getting the following error:

Error Message:
MdxScript(Model) (24, 20) Calculation error in measure 'Log'[last index in view]: DAX comparison operations do not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

I have uploaded the excel file and the powerbi file to a dropbox link at the top of my post if you want to give it another try. Thanks again!