Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Patryk
Regular Visitor

DISTINCT COUNTROW based on value

Hi, New PBI user here.

 

I have a list of SITE URLs, assigned to parts of the company. I need to present where the ownership is missing. Example data to ilustrate:

 

Site Collection URLAreaOwner Status
SCOM2012Area 3 
ASAAA0065Area 2Owner Missing
ASAAA0066Area 3 
ASAAA0073Area 3 
ASAAA0074Area 2Owner Missing
ASAAA0074Area 2Owner Missing
ASAAA0084Area 3 
ASAAA0084Area 3 
ASAAA0085Area 1Owner Missing
ASAAA0085Area 1Owner Missing
ASAAA0088Area 3 
ASAAA0088Area 3 

 

As you can see some sites are presented twice (can have two owners missing) but I only need to count these sites as one.

 

I can arrive in my desired number when using clustered chart visualisation with Area as Axis, distinct count of SITE URL as value and Owner Status as filter to the value of "Owner Missing".

 

The issue is, I want to show this value as percentage of all sites per area so I need to calculate a measure with the same results.

 

What I have currently is  Measure = CALCULATE ( COUNTROWS ('Sites'), 'Sites'[Owner Status] = "Owner Missing") but that simply adds +1 wherever Owner Missing is present so I somehow need to add the SIte URL check for DISTINCT COUNT but can't do it in any correct way.

 

Any help appreciated!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Measure = CALCULATE(DISTINCTCOUNT(Sites[Site Collection URL]), Sites[Owner Status] = "Owner Missing") 

You can show a percentage if you select 'show value as-> percent of grand total' from the dropdown in the Values field well.

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

Measure = CALCULATE(DISTINCTCOUNT(Sites[Site Collection URL]), Sites[Owner Status] = "Owner Missing") 

You can show a percentage if you select 'show value as-> percent of grand total' from the dropdown in the Values field well.

Works great, thank you Chilli

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.