Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
, 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?
You need to update your version of Power BI Desktop. I think INDEX was introduced in the December 2022 update.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |