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

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.

     

3 Replies

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

       

  • sevenhills's avatar
    sevenhills
    Super User
    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!