cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SarathB2
Frequent Visitor

Need urgent help on Dax logic to filter some data

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:

IDDateVersionActionEventResponse
111/26/2022V1NewCOSuccess
211/26/2022V1NewFXFail
311/26/2022V1NewCOFail
311/26/2022V2NewCOSuccess
411/27/2022V1NewCOSuccess
511/27/2022V1NewFXFail
511/28/2022V2NewFXSuccess

 

Expected result data:

IDDateVersionActionEventResponse
111/26/2022V1NewCOSuccess
211/26/2022V1NewFXFail
311/26/2022V2NewCOSuccess
411/27/2022V1NewCOSuccess
511/28/2022V2NewFXSuccess

(
where 

311/26/2022V1NewCOFail
511/27/2022V1NewFX

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 @Eyelyn9 @MFelix @evahohk @yingyinr @HotChilli @MasterSonic @ryan_mayu @VijayP @petr @ImkeF @EarlCD @v-mengzhu-msft   @Zubair_Muhammad    @parry2k @tamerj1 @Jihwan_Kim  @daXtreme @PBICommunity 

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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.

1.png

Filter Measure = 
VAR CurrentVerion = 
    SELECTEDVALUE ( 'Table'[Version] )
VAR LastVersion = 
    CALCULATE ( 
        MAX ( 'Table'[Version] ), 
        ALL ( 'Table' ), 
        VALUES ( 'Table'[ID] ) 
    )
RETURN
    IF ( 
        CurrentVerion = LastVersion,
        1
    )

View solution in original post

PawarNovil
Frequent Visitor

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.

 

PawarNovil_0-1669706846501.png

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)

---------------------------------------------------------------

PawarNovil_1-1669707025000.png

 

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.

PawarNovil_2-1669707514901.png

 

Step-2 Joining or merging  Old table with new grouping table and take the Count column to create flag using custom column.

PawarNovil_3-1669707657855.png

Step-3 Creating Flag column-

 

PawarNovil_4-1669707809537.png

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-

PawarNovil_5-1669707965558.png

 

Regards,

Novil

If I answer your question, please mark my post as a solution.

 

 

View solution in original post

11 REPLIES 11
PawarNovil
Frequent Visitor

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.

 

PawarNovil_0-1669706846501.png

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)

---------------------------------------------------------------

PawarNovil_1-1669707025000.png

 

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.

PawarNovil_2-1669707514901.png

 

Step-2 Joining or merging  Old table with new grouping table and take the Count column to create flag using custom column.

PawarNovil_3-1669707657855.png

Step-3 Creating Flag column-

 

PawarNovil_4-1669707809537.png

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-

PawarNovil_5-1669707965558.png

 

Regards,

Novil

If I answer your question, please mark my post as a solution.

 

 

parry2k
Super User
Super User

@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






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.





tamerj1
Super User
Super User

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.

1.png

Filter Measure = 
VAR CurrentVerion = 
    SELECTEDVALUE ( 'Table'[Version] )
VAR LastVersion = 
    CALCULATE ( 
        MAX ( 'Table'[Version] ), 
        ALL ( 'Table' ), 
        VALUES ( 'Table'[ID] ) 
    )
RETURN
    IF ( 
        CurrentVerion = LastVersion,
        1
    )
VijayP
Super User
Super User

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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

@tamerj1 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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?

Jayee
Responsive Resident
Responsive Resident

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
Continued Contributor
Continued Contributor

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!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors