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 

To filter rows where each Project ID and Market combination has exactly two Parcel Types:

1. Create a Measure in Power BI: RowsWithTwoParcelTypes =
   VAR ParcelTypeCount =
   CALCULATE( DISTINCTCOUNT(ProjectsData[Parcel Types]),
   ALLEXCEPT(ProjectsData, ProjectsData[Project ID], ProjectsData[Market]))
   RETURN
   IF(ParcelTypeCount = 2, 1, 0)
   
   

2. Apply as Filter:

   • Add the measure to the visual filter paane.
   • Set the filter condition to show only rows where RowsWithTwoParcelTypes = 1.

[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.