How to get most recent record by id in a table and then count records

[Mi_80]

Hi,

I am really struggling to find a way to take a table of updates, then group those updates by an ID, and only take the latest records for each id and use that data to count and visualise (which can be dynamically filtered).

So for example a table of updates

update date	category type	id
1/1/21	normal	1
3/1/21	low	1
1/1/21	high	2
2/1/21	normal	2
4/1/21	low	2
2/1/21	high	1

so I would like to turn this (dynamically) to if filtered date = 2/1/21

update date	category type	id
2/1/21	high	1
2/1/21	normal	2

so I only have the most recent entry for each ID.

Then I would like to take that data to be able to count and visualise e.g. Count ID that have category type normal...in above case equal 1 and show this is bar chart or line graph where I can show the count of category types each month, based on most rcent records.

Hope this makes sense.

Do far using the below measure I am able to create a table woth the most rcent category type by distinct partner id

Measure 2 =

CALCULATE(FIRSTNONBLANK(new_partnerlandscapehistory[Value],1),FILTER(new_partnerlandscapehistory,new_partnerlandscapehistory[createdon] = MAX(new_partnerlandscapehistory[createdon])))

 Thanks for your help

[Mi_80]

, I saw this solution in a different thread and the logic looks like what I need but when I try to use the INDEX function it says it doesn't exist...any ideas why?

[johnt75]

You need to update your version of Power BI Desktop. I think INDEX was introduced in the December 2022 update.

[johnt75]

You could create a measure like

Num IDs =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Table',
            ORDERBY ( 'Table'[update date], DESC ),
            PARTITIONBY ( 'Table'[id] )
        ),
        'Date'[Date] <= ReferenceDate
    )
VAR Result =
    COUNTROWS ( SummaryTable )
RETURN
    Result

To stop Power BI complaining about possible duplicate rows you may need to add a column to your fact table to uniquely identify each row. If you don't already have a column like that you can use Power Query to add an index column. In the Model view select the fact table and mark the unique column as the key for the table.