Need urgent help on Dax logic to filter some data

[SarathB2]

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 

[tamerj1 · Accepted 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
    )

[PawarNovil · Accepted 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.

[parry2k]

@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

[tamerj1 · Accepted 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
    )

[VijayP]

@SarathB2 are These records are unique or having duplicates except when same record can be fail and success !

[SarathB2]

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. 

[VijayP]

@SarathB2  I suggest to use @tamerj1  Solution by chaning the combination of uniqueness and it should work!

[tamerj1]

@VijayP 
The problem is @SarathB2 has mensioned that the next version might be in the next day. In this case this shall not work. Rather it will return both versions. We need to think of something else.

[VijayP]

@tamerj1 

With your solution it should be possible to achieve by creating a combination of all columns as another column !

[tamerj1]

@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?

[Jayee]

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!!

[NikhilChenna]

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!