Forum Discussion

Vishruti's avatar
Vishruti
Helper I
1 year ago
Solved

Measure to exclude rows based on multiple columns

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 onl...
Show and Tell
  • sevenhills's avatar
    sevenhills
    1 year ago

    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!

  • gmsamborn's avatar
    gmsamborn
    1 year ago

    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.

     

rohit1991's avatar
rohit1991
Super User
1 year ago

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's avatar
gmsamborn
Super User
1 year ago

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.