Need help excluding certain rows based on value in another column

[Rickterpstra]

Hi all, 

I recently started using PowerBI and I am running into an issue now. 

I have a database full of offer/quotation data. I want to make a report that shows the number of accepted offers, denied offers as well as a percentage based report. 

In the database, every offer is a unique row. If, for example, an offer is denied because the client wants a small change, that offer is marked as 'Denied' and a new version (copy) of that offer is created. However, if that second offer is accepted we still want to count the offer in general as 'Accepted'. The succes rate for that offer is 100%. We don't want to count changes as a denial. 

In the database the following information is available: 

OfferID	Status	OriginalOfferID
1	D	null
2	D	1
3	A	1

In the case above the client has requested an offer for a garden overhaul. Let's say in the first offer a small mistake was made in the calculation. This offer is marked as denied (D) and a copy with id 2 is made. This one, unfortunately, has some mistakes in the text which means it is registered as denied (D) again and a third copy is created. This third copy is eventually accepted (A). For our report we want to see the offer for this clients garden as a 100% result. We don't want to count the 2 denied offers as actually denied. 

Next to the statusses D & A there are two more available statusses, "O" (Outstanding) and "C" (Concept). 

In SQL I use the following Query to exclude all offers that have been denied but do have a new copy of the offer: 

Select * from Offers O1 where Status = 'D' and OriginalOffer_ID is null and not exists (select * from Offers O2 where status in ( 'O', 'C') and O1.ID = O2.OriginalOffer_ID) order by ID desc

So basically I want to find all offers that have the status "D" (Denied) and OriginalOffer_ID is blank. But, in addition, I want to exclude all offers that match these conditions if their "ID" is present somewhere in the column "OriginalOffer_ID" of other records with a different status. 

I have tried to search the communit but couldn't find an answer, please let me know what to do.

[AlB · Accepted solution]

@Rickterpstra 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIB4rzSnBwgZWSgFKsTrWQEFTVECBkDmY6oQiZoeg3BoqZApj8QmyCEzNAUmoBFzYFMZyRRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OfferID = _t, Status = _t, OriginalOfferID = _t, ClientID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OfferID", Int64.Type}, {"Status", type text}, {"OriginalOfferID", Int64.Type}, {"ClientID", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([OriginalOfferID] = null) and ([Status] = "D")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Table.RowCount(Table.SelectRows(#"Changed Type", (inner)=> inner[Status]<>[Status] and inner[OriginalOfferID]=[OfferID]))=0)
in
    #"Filtered Rows2"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

I have no idea what you are doing. Works fine on my end. See attached

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Rickterpstra]

This doesn't help. 

OfferID	Status	OriginalOfferID	ClientID
1	D	null	20
2	D	1	20
3	A	1	20
4	D	null	20
5	O	4	20
6	D	null	20
7	C	null	20

The first filter has 3 results: offer 1, 4 and 6. 

However, the second filter does not eliminate offer 1 and 4. This on should not be counted as Denied because the second/third version was actually accepted or still outstanding.

[AlB · Accepted solution]

@Rickterpstra 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIB4rzSnBwgZWSgFKsTrWQEFTVECBkDmY6oQiZoeg3BoqZApj8QmyCEzNAUmoBFzYFMZyRRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OfferID = _t, Status = _t, OriginalOfferID = _t, ClientID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OfferID", Int64.Type}, {"Status", type text}, {"OriginalOfferID", Int64.Type}, {"ClientID", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([OriginalOfferID] = null) and ([Status] = "D")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Table.RowCount(Table.SelectRows(#"Changed Type", (inner)=> inner[Status]<>[Status] and inner[OriginalOfferID]=[OfferID]))=0)
in
    #"Filtered Rows2"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Rickterpstra]

Getting closer but the code above still doesn't give me the result i need. 

Client ID is not a relevant factor here, the table cloud have looked just like this: 

OfferID	Status	OriginalOfferID	ClientID
1	D	null	20
2	D	1	20
3	A	1	20
4	D	null	20
5	O	4	20
6	D	null	20
7	C	null	20

The result however is still the same. If these were all the records in the database the total number of actual 'Denied" offers would be 1, i.e. order 6. 

I need all offers with status "D" where OriginalOfferID = null provided that the 'ID' of these records doesn't show anywhere in te column "OriginalOfferID" for offers with a status other than "D". If it does that offer should be excluded.

[Rickterpstra]

Hi AlB, 

Thx for your response. Let me expand the table a bit. 

OfferID	Status	OriginalOfferID	ClientID
1	D	null	20
2	D	1	20
3	A	1	20
4	D	null	21
5	O	4	21
6	D	null	24
7	C	null	28

In this case I only want offer 6 to be counted as 'Denied' as this offer doesn't have any follow-up offer. 
- Offer 1,2,3 are practically the same offer, however, because of some mistakes they had to be markes denied and a new version was created. 
- Offer 4,5 cannot be counted as Denied because a copy of the offer is still outstanding. Only when this one would also be denied it can count as a Denied offer.
- Offer 7 is still in Concept status

I do want this in PQ yes. 

[AlB]

@Rickterpstra 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIB4rzSnBwgZWSgFKsTrWQEFTVECBkDmY6oQiZoeg3BoqZApj8QmyCEzNAUmoBFzYFMZyRRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OfferID = _t, Status = _t, OriginalOfferID = _t, ClientID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OfferID", Int64.Type}, {"Status", type text}, {"OriginalOfferID", Int64.Type}, {"ClientID", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([OriginalOfferID] = null) and ([Status] = "D")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Table.RowCount(Table.SelectRows(#"Changed Type", (inner)=> inner[ClientID] = [ClientID] and inner[Status]<>[Status]))=0)
in
    #"Filtered Rows2"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jimmy801]

Hello @Rickterpstra 

you have to create measures that do exactly that what you want. For this you can use a Calculate and in the filter section, filter the table according to your needs. Here a practicable example (not tested)

DeniedOffers= Calculate(CountRows(YourTable),Filter(YourTable, [Status]="D"&&[OriginalOfferID]="null"))

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlB]

Hi @Rickterpstra 

How do you identify the client in your data?

To help clarify, please show a sample table that includes all the cases (status O and C as weel) and show the expected result for that sample.

Do you want this in PQ ?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers