Forum Discussion
Vishruti
1 year agoHelper I
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...
- 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!
- 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] ) > 1It 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
1 year agoSuper User
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!