Measure to exclude rows based on multiple columns

[Vishruti]

I have following type of data where for one project, and its one market there may be multiple Parcels Types or a single Parcel Type.

When I plot this data in Power BI visual, I want to retain only those rows where for one project ID and its market there are two Parcel Types (like all rows highlighted in red below)

How can I achieve this? I was hoping to get assistance with a measure that I can add as a filter on the visual.

[sevenhills · Accepted solution]

See this approach by creating the measure and applying filter:

Multiple Rows Project ID = 
var _a = CALCULATE( COUNTROWS(), ALLEXCEPT( 'Table', 'Table'[Project ID], 'Table'[Market]))
RETURN IF(_a > 1, 1, BLANK())

Optional: Create another measure to know the count

Count Rows Project ID - Market =  
CALCULATE( COUNTROWS(), ALLEXCEPT( 'Table', 'Table'[Project ID], 'Table'[Market]))

Hope it helps!

[sevenhills · Accepted solution]

See this approach by creating the measure and applying filter:

Multiple Rows Project ID = 
var _a = CALCULATE( COUNTROWS(), ALLEXCEPT( 'Table', 'Table'[Project ID], 'Table'[Market]))
RETURN IF(_a > 1, 1, BLANK())

Optional: Create another measure to know the count

Count Rows Project ID - Market =  
CALCULATE( COUNTROWS(), ALLEXCEPT( 'Table', 'Table'[Project ID], 'Table'[Market]))

Hope it helps!

[rohit1991]

Hi, @Vishruti 

measure that checks if each Project ID + Market combination has exactly two Parcel Types. Here’s how you can do it:

Step 1: Create the Measure

RowsWithTwoParcelTypes =
VAR ParcelTypeCount =
    CALCULATE(
        DISTINCTCOUNT(ProjectsData[Parcel Types]),
        ALLEXCEPT(ProjectsData, ProjectsData[Project ID], ProjectsData[Market])
    )
RETURN
    IF(ParcelTypeCount = 2, 1, 0)

Step 2: Use as a Visual Filter Add this measure to your visual. Set the filter to show only rows where RowsWithTwoParcelTypes = 1.

This will keep only those (Project ID, Market) pairs where there are exactly two Parcel Types, just like your highlighted example. If you need to allow more than two Parcel Types, just change = 2 to >= 2 or whatever fits your needs.

[gmsamborn · Accepted solution]

Hi @Vishruti 

Here is another measure / example.

Flag = 
	MAXX(
		ADDCOLUMNS(
			SUMMARIZE(
				'ProjectsData',
				[Project ID],
				[Market]
			),
			"__Cnt", CALCULATE( DISTINCTCOUNT( 'ProjectsData'[Parcel Types] ) )
		),
		[__Cnt]
	) > 1

It will return TRUE for any combinations that have more than 1 Parcel Type.  If there is only 1 Parcel Type then it will return FALSE.

Let me know if you have any questions.