Latest Record Based on Date Slicer

[Anonymous]

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 want to be able to see totals based on the max value of a date slicer.

To better explain, I was able to do this in excel with some logic and an array formula.

I have a log of changes for employees, simplified example to only show 1 Attribute. Each row is a new record with a specific date, and is a new Attribute from that point until the next record for that employee.

In a cell, I can specify a date I want to look at, and with logic I know which records are true, or "In View."

I then have an array formula that will tell me for each employee what record in view is the latest.

And then in a separate table where I have the list of employees, and I look up the latest index in view for each and then lookup the attributes on that index.

It is then trivial to count up totals for all the attributes and see those totals change as I change the Date that I am looking at.

Methods attempted in PowerBI:

Loading data to PowerBI keeping only ID, Record Date, and Attribute and create a generated Index in the Query Editor.

I have set up measures that check the record date against the date slicer,

In View = IF( MAX('Log'[Record Date])<=MAX(DateKey[Date])&&MAX('Log'[Record Date])>=MIN(DateKey[Date]) ,1,0)

Return the max index for each employee,

Max Index = MAXX(VALUES('Log'), IF([In View]=1,'Log'[Index],0))

And then lookup the attribute.

Latest Attribute = LOOKUPVALUE('Log'[Attribute],'Log'[Index],[Max Index]) 

As I adjust my date slicer, I can see these attribute and max index changing properly. However, if I check attribute 1 in another slicer, it will return 1 for all 5 employees as all 5 employees have a record with the attribute 1 somewhere in the table.

What i would like to see as the result by slicing 1 is Employee ID 1, 2, and 4 only.

I suspect the order of operations is wrong. This slicer and other measures, counters, etc are filtering down the table and then the measure is finding the latest record of that new context. This is messing up counts and all further use of the model.

I need the latest record against the date slicer regardless of other contexts that happen.

I have attempted to reorder the filtering by using variables, setting up a virtual table with CALCULATETABLE, and I am having major trouble with the syntax.

Much thanks for the help!

[v-juanli-msft]

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]

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]

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]

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 · Accepted solution]

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.

[amitchandak]

@Anonymous , Did not get it completely

But try like

Max Index = CALCULATE(max('Log'[Atribute]),filter('Date','Date'[Date]=max('Date'[Date])))
OR
Max Index = CALCULATE(max('Log'[Atribute]),filter('Date','Date'[Date]=lastdate('Date'[Date])))

[smarthp29]

@amitchandak  In this final table that is obtained after applying the date slicer, when we select Attribute 1 in the slicer, the table should show Employee ID 1, 2 and 4. I think this is the desired end goal.

[Greg_Deckler]

Sample data as text would be extremely helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thanks Greg, I encountered the table paste error in trying to edit my original post so here is the sample data table:

Index	Employee ID	Record Date	Attribute
1	000000001	3/1/2020	1
2	000000001	3/31/2020	2
3	000000001	4/5/2020	1
4	000000002	3/1/2020	2
5	000000002	3/31/2020	1
6	000000003	3/15/2020	1
7	000000003	3/31/2020	3
8	000000004	3/15/2020	1
9	000000004	4/15/2020	3
10	000000005	3/1/2020	1
11	000000005	4/1/2020	2