Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello All,
I am creating a dashboard using an SQL Database, so there is no Power Query where I can work on an data query.
In the dataset I have several alphanumeric IDs with several versions of them. But I need only the last versions of all these IDs. So I need to remove the duplicates of these IDs with the consideration that I need only the last versions.
For example, the alphanumeric ID is ABC2010001111, this one has 4 versions, 00, 01, 02, 03.
I only need the latest and most updated version to have a reliable dashboard. So ABC2010001111/03 is the one I keep. all others must be removed.
Another might have 20 versions, or 2 versions. So there is no point in saying "use always thr 4th version."
Any idea how this might work?
Thank you in advance,
Best regards,
Nursen
Solved! Go to Solution.
@dienursen
Hi, since you are using sql source as direct query, 1st approach is to get the solution form sql query it self.
WITH LatestVersions AS (
SELECT
ID,
Version,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Version DESC) AS RowNum
FROM
YourTable
)
SELECT
ID,
Version
FROM
LatestVersions
WHERE
RowNum = 1;
or 2nd approach is to use DAX
Create a Calculated Table:
LatestVersions =
SELECTCOLUMNS(
SUMMARIZE(
'YourTable',
'YourTable'[ID],
"LatestVersion", MAX('YourTable'[Version])
),
"ID", [ID],
"Version", [LatestVersion]
)
and then calculated column
LatestVersion =
CALCULATE(
MAX('YourTable'[Version]),
ALLEXCEPT('YourTable', 'YourTable'[ID])
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
@dienursen
Hi, since you are using sql source as direct query, 1st approach is to get the solution form sql query it self.
WITH LatestVersions AS (
SELECT
ID,
Version,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Version DESC) AS RowNum
FROM
YourTable
)
SELECT
ID,
Version
FROM
LatestVersions
WHERE
RowNum = 1;
or 2nd approach is to use DAX
Create a Calculated Table:
LatestVersions =
SELECTCOLUMNS(
SUMMARIZE(
'YourTable',
'YourTable'[ID],
"LatestVersion", MAX('YourTable'[Version])
),
"ID", [ID],
"Version", [LatestVersion]
)
and then calculated column
LatestVersion =
CALCULATE(
MAX('YourTable'[Version]),
ALLEXCEPT('YourTable', 'YourTable'[ID])
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
@dienursen , You can not do this in Power Query in case of direct query mode, you need have DAX measure to display
Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |