Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Experts,
Good day!
I have connected the data with Direct Query (which is difficult for some transformations and creating calculated columns).
I have a data which they submits daily to the database based on deals which decides 'Success' or 'Fail' in response.
If any deal will be Fail in Response , then they will resubmit that Fail deal may be same day or next day with the new version which will change response to 'Success'. Then both Fail and Success records for same ID will be avilable in our table. If previous Fail deal will change the response as Success with new version ,then we need to avoid(filter) that fail deal in the table.
Sample data:
ID | Date | Version | Action | Event | Response |
1 | 11/26/2022 | V1 | New | CO | Success |
2 | 11/26/2022 | V1 | New | FX | Fail |
3 | 11/26/2022 | V1 | New | CO | Fail |
3 | 11/26/2022 | V2 | New | CO | Success |
4 | 11/27/2022 | V1 | New | CO | Success |
5 | 11/27/2022 | V1 | New | FX | Fail |
5 | 11/28/2022 | V2 | New | FX | Success |
Expected result data:
ID | Date | Version | Action | Event | Response |
1 | 11/26/2022 | V1 | New | CO | Success |
2 | 11/26/2022 | V1 | New | FX | Fail |
3 | 11/26/2022 | V2 | New | CO | Success |
4 | 11/27/2022 | V1 | New | CO | Success |
5 | 11/28/2022 | V2 | New | FX | Success |
(
where
3 | 11/26/2022 | V1 | New | CO | Fail |
5 | 11/27/2022 | V1 | New | FX | Fail |
these records need to filter out from our table visual, becoz they submitted again and those are Success now.
)
@d_gosbell @amitchandak @Greg_Deckler @Luis_Ma @Jayee @bluefalcon @PijushRoy @Senthil_Kumar @v-jianboli-msft @PawarNovil @Umair_Aslam @shreyamukkawar @v-eqin-msft @MFelix @evahohk @v-yiruan-msft @HotChilli @MasterSonic @ryan_mayu @VijayP @petr @ImkeF @EarlCD @v-mengzhu-msft @Zubair_Muhammad @parry2k @tamerj1 @Jihwan_Kim @daXtreme @PBICommunity
Solved! Go to Solution.
Hi @SarathB2
Please refer to attached sample file with the solution.
Create a filter measure, place it in the filter pane of the table visual, select "is not blank" then apply the filter.
Filter Measure =
VAR CurrentVerion =
SELECTEDVALUE ( 'Table'[Version] )
VAR LastVersion =
CALCULATE (
MAX ( 'Table'[Version] ),
ALL ( 'Table' ),
VALUES ( 'Table'[ID] )
)
RETURN
IF (
CurrentVerion = LastVersion,
1
)
Hello @SarathB2 ,
If you really don't need that unwanted "Fail" row then it is better to remove that from or as close as possible to the data source.
Solution - 1 As you mention you are using a Direct Query if in case your source is an SQL database then I have an SQL query to remove unwanted rows.
That you can use, when you are connecting to database-
----------------------------------------------------------------
SELECT R.[ID]
,[DATE]
,[VERSION]
,[EVENT]
,[ACTION]
,[RESPONSE]
,A.CNT
FROM [HackerRank].[dbo].[RESPONSE] R
LEFT JOIN
(SELECT ID, COUNT(ID) CNT FROM [RESPONSE]
GROUP BY ID) A
ON R.ID=A.ID
WHERE CNT = 1 OR (RESPONSE='SUCCESS' AND CNT>1)
---------------------------------------------------------------
Solution-2 Using Query editor removing unwanted rows-
Step-1 Creating new table with the ID and count of rows(fail or Success) present for that id. You can do that by simply Group by on ID column and aggregation as count.
Step-2 Joining or merging Old table with new grouping table and take the Count column to create flag using custom column.
Step-3 Creating Flag column-
if [Count]=1 or ([Count]>1 and [RESPONSE]="Success") then "YES" else "NO"
Now you can filter out unwanted rows and work on latest data.
Final Output-
Regards,
Novil
If I answer your question, please mark my post as a solution.
Hello @SarathB2 ,
If you really don't need that unwanted "Fail" row then it is better to remove that from or as close as possible to the data source.
Solution - 1 As you mention you are using a Direct Query if in case your source is an SQL database then I have an SQL query to remove unwanted rows.
That you can use, when you are connecting to database-
----------------------------------------------------------------
SELECT R.[ID]
,[DATE]
,[VERSION]
,[EVENT]
,[ACTION]
,[RESPONSE]
,A.CNT
FROM [HackerRank].[dbo].[RESPONSE] R
LEFT JOIN
(SELECT ID, COUNT(ID) CNT FROM [RESPONSE]
GROUP BY ID) A
ON R.ID=A.ID
WHERE CNT = 1 OR (RESPONSE='SUCCESS' AND CNT>1)
---------------------------------------------------------------
Solution-2 Using Query editor removing unwanted rows-
Step-1 Creating new table with the ID and count of rows(fail or Success) present for that id. You can do that by simply Group by on ID column and aggregation as count.
Step-2 Joining or merging Old table with new grouping table and take the Count column to create flag using custom column.
Step-3 Creating Flag column-
if [Count]=1 or ([Count]>1 and [RESPONSE]="Success") then "YES" else "NO"
Now you can filter out unwanted rows and work on latest data.
Final Output-
Regards,
Novil
If I answer your question, please mark my post as a solution.
@SarathB2 check this video on my YT channel which will help you to get the result How to get value of each product based on the most recent transaction - Power BI - YouTube
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @SarathB2
Please refer to attached sample file with the solution.
Create a filter measure, place it in the filter pane of the table visual, select "is not blank" then apply the filter.
Filter Measure =
VAR CurrentVerion =
SELECTEDVALUE ( 'Table'[Version] )
VAR LastVersion =
CALCULATE (
MAX ( 'Table'[Version] ),
ALL ( 'Table' ),
VALUES ( 'Table'[ID] )
)
RETURN
IF (
CurrentVerion = LastVersion,
1
)
@SarathB2 are These records are unique or having duplicates except when same record can be fail and success !
Proud to be a Super User!
Hi @VijayP , based on ID, Date, version it will be unique( in combination).
ID may repeat for different dates, Dates may repeat for different ID, and Version will change on evry submission. In all combination it will be unique.
@SarathB2 I suggest to use @tamerj1 Solution by chaning the combination of uniqueness and it should work!
Proud to be a Super User!
With your solution it should be possible to achieve by creating a combination of all columns as another column !
Proud to be a Super User!
@VijayP
You may be correct. However, I think we need to have a look at a little bit larger sample of data before jumping into conclusions.
@SarathB2 would you please provide a larger sample of data that includes multiple dates of the same ID's and perhaps more than two versions? By the way, what is the maximum possible number of versions?
Hi @SarathB2 ,
As you mentioned you are using direct query i suggest to create a active flag in Database table for only latest response by data and group by other fields( Version, Action, Event).
Create a table visual in Power BI and use filter this visual and set active flag=1.
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
Hi @SarathB2 ,
I think the solution for you is to exclude the fail entries at the source by using Rank function and based and Event and date.
Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
41 | |
31 | |
25 | |
23 | |
23 |