Forum Discussion

lc1's avatar
lc1
Icon for Helper III rankHelper III
2 years ago

Remove duplicates if condition is met

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 ...
Need Help
gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

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's avatar
lc1
Icon for Helper III rankHelper III
2 years ago

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 1Table 2Table 3Table 4 
Completion DateTotal 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.