Filtering

[Anonymous]

I want to filter the job numbers based on the the type of attachment uploaded in the project attachment table (subject= A or subject= B). The users upload attachments with quotes, invoices, etc and title the subject type "A", but then upload an empty attachment with the subject as type "B". I need to be able look at all attachments for that one job number and if it has an attachment with the B subject then call it a type B job. I'm not sure if thats possible with the way data is read in Power BI, but if anyone has suggestions I would appreciate it. Thanks!

[PaulDBrown]

Please provide sample data or a non-confidential PBIX file, and a depiction of what you are trying to achieve. Thanks!

[Anonymous]

Sorry heres a visual of what I'm working with

Project Table:

ProjectID	Jobnumber
1000	1
1001	2
1002	3

ProjectAttachment Table:

ProjectAttachmentID	ProjectID	Subject	Vendor Cost	Contractor Cost	Estimated Hrs
200001	1000	"Customer Quote"	100	100	2
200003	1001	"Customer Quote"	500	500	10
200004	1001	"Customer T&M"	0	0	0
200005	1002	"Customer T&M"	10	10	1

So in this example ProjectID 1001 has both a Quote and T&M attachment uploaded, but it is still considered a T&M project

I'm hoping to have a True/False column on whether its a "T&M" project like so:

ProjectAttachment Table:

ProjectID	Jobnumber	Is T&M
1000	1	N
1001	2	Y
1002	3	Y

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check this formula.

flag = if(CONTAINSSTRING(ProjectAttachment[Subject],"T&M"),"Y","N")

Column = MAXX(filter(ProjectAttachment,ProjectAttachment[ProjectID]=EARLIER(ProjectAttachment[ProjectID])),[flag])

Best Regards,

Jay

[Anonymous]

In the column formula, it wouldn't let me put "Earlier= projectattachment[projectID]" so I replaced it with "Earlier= Project[ProjectID]" and it worked, thanks for the help!