Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |