Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
89 | |
73 | |
69 | |
65 | |
57 |
User | Count |
---|---|
96 | |
92 | |
91 | |
75 | |
69 |