topic Re: Need urgent help on Dax logic to filter some data in DAX Commands and Tips

Need urgent help on Dax logic to filter some data

SarathB2 — Mon, 28 Nov 2022 07:03:26 GMT

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 Anonymous Anonymous MFelix evahohk Anonymous HotChilli MasterSonic ryan_mayu VijayP petr ImkeF EarlCD Anonymous Zubair_Muhammad parry2k tamerj1 Jihwan_Kim daXtreme PBICommunity

Re: Need urgent help on Dax logic to filter some data

NikhilChenna — Mon, 28 Nov 2022 06:59:46 GMT

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!

Re: Need urgent help on Dax logic to filter some data

Jayee — Mon, 28 Nov 2022 13:33:18 GMT

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

Re: Need urgent help on Dax logic to filter some data

VijayP — Mon, 28 Nov 2022 07:09:15 GMT

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

Re: Need urgent help on Dax logic to filter some data

tamerj1 — Mon, 28 Nov 2022 07:21:41 GMT

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 )

Re: Need urgent help on Dax logic to filter some data

SarathB2 — Mon, 28 Nov 2022 07:24:26 GMT

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.

Re: Need urgent help on Dax logic to filter some data

VijayP — Mon, 28 Nov 2022 07:26:58 GMT

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

Re: Need urgent help on Dax logic to filter some data

tamerj1 — Mon, 28 Nov 2022 07:51:40 GMT

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.

Re: Need urgent help on Dax logic to filter some data

VijayP — Mon, 28 Nov 2022 07:55:25 GMT

tamerj1

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

Re: Need urgent help on Dax logic to filter some data

tamerj1 — Mon, 28 Nov 2022 08:07:58 GMT

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?

Re: Need urgent help on Dax logic to filter some data

parry2k — Mon, 28 Nov 2022 15:48:51 GMT

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

Re: Need urgent help on Dax logic to filter some data

PawarNovil — Tue, 29 Nov 2022 07:50:10 GMT

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.