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.

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.