Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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!
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.
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!
Hi, @Vishruti
To filter rows where each Project ID and Market combination has exactly two Parcel Types:
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)
Apply as Filter:
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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |