Remove duplicates if condition is met

[lc1]

Hello,

I have two columns (Project Id) and (Fund).

I need to filter only projects that are funded 100% by PTP not partially funded.

Can someone advise the best way to obtain these results?

Thank you.

Proj ID	Fund
75636	PTP	Ok
3000020	Grant
3000020	PTP
3000020	Operating
3002541	Operating
3003301	PTP
3003301	Grant
3003861	PTP	Ok

[Anonymous]

Hi @lc1 ,

May I ask if the solution mentioned in the link you provided helped you?

Best Regards,
Adamk Kong

[lc1]

Hi, it did but not completely. But I still found it very useful.

For the task that I had, since I had a tight deadline, I ended up doing it in excel.

[lc1]

Good morning,

As mentioned before, the recommendations provided do not bring the expected results. I appreciate your help.

I wish I could've sent a sample of the file but since it has multiple tables related it is very time consuming to recreate.

However, here is a link I found that might help others with a similar problem:

Solved: Re: If Duplicate Value In Column vs Distinct Occur... - Microsoft Fabric Community

[Ashish_Mathur]

Hi,

Write these measures

Financier count = DISTINCTCOUNT(Data[Fund])

Test = 1*(AND([Financier count]=1,MIN(Data[Fund])="PTP"))

Drag the Test measure to the filter section and apply a condition of 1.

Hope this helps.

[ThxAlot]

[NaveenGandhi]

Hi @lc1 

Please use below measure.

PTP=

VAR PTPProjects =

    SUMMARIZE(

        FILTER(

            'table',

            CALCULATE(

                COUNTROWS('table'),

                ALLEXCEPT('table', 'table'[Proj ID])

            ) = CALCULATE(

                COUNTROWS('table'),

                ALLEXCEPT('table', 'table'[Proj ID]),

                'table'[Fund] = "PTP"

            )

        ),

        'table'[Proj ID]

    )

RETURN

    if(SELECTEDVALUE('Table'[Proj ID]) IN PTPProjects,"Ok","")

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[gmsamborn]

Hi @lc1 

Would a measure like this help?

Flag = 
VAR _Proj = MAX( 'Table1'[Proj ID] )
VAR _Table =
    FILTER(
        ALL( 'Table1' ),
        'Table1'[Proj ID] = _Proj
    )
VAR _All = COUNTROWS( _Table )
VAR _PTP = 
    COUNTROWS(
        FILTER(
            _Table,
            'Table1'[Fund] = "PTP"
        )
    )
VAR _Logic = IF( _All = _PTP, 1 )
RETURN
    _Logic

That flag can be used to filter your visual.

Remove duplicates if condition is met.pbix

[lc1]

Thank you all for the responses, I tried the various solutions but could not get the results I'm looking for.

I should've mentioned I'm working with data coming from 4 different tables:

Table 1	Table 2	Table 3	Table 4
Completion Date	Total Expenditures	*Project ID	* Revenue Name
* Distinct value (key)

I need to create two matrixes, one with a list of projects that are 100% funded by Revenue type 1 and one table with projects that are fully and partially funded with Revenue type 1.

Meaning, the second table should have all projects funded 100% with Rev type 1 in addition to other projects funded with other types of revenue.

[gmsamborn]

Hi @lc1 

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.