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 

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's avatar
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!

  • 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.