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
rweb95
Frequent Visitor

Column Distinct Count with Several Conditions

Hello everyone!

 

I need help getting a distinct count of names within a column with several conditions applied.

Here is a sample of the data I'm working with:

 

UtilityNameRateScheduleNameIsDefaultIsActiveCompanyId

F Electric

Husker False11
F ElectricGeneral ServiceTrueTrue 
F ElectricGeneral ServiceTrueTrue 
F ElectricGlacial EnergyFalseTrue12
F ElectricIrrigationTrueTrue 
F ElectricIrrigationTrueTrue 
F ElectricLarge CommercialTrueTrue 
F ElectricLarge CommercialTrueTrue 
F ElectricLarge Commercial Load ControlTrueTrue 
F ElectricLarge Commercial Load ControlTrueTrue 
F ElectricResidential & Small BusinessTrueTrue 
F ElectricResidential & Small BusinessTrueTrue 

 

I want to count the number of distinct names in the 'RateSchedule - Current'[RateScheduleName]

 

Here is the formula I tried to use:

 

NumberOfRates =

IF('RateSchedule - Current'[IsActive] = TRUE()
&& 'RateSchedule - Current'[CompanyId] = BLANK()
&& 'RateSchedule - Current'[IsDefault] = TRUE(),
DISTINCTCOUNT('RateSchedule - Current'[RateScheduleName]))
 
From the table above, F Electric should have a distinct count of 5 in the 'RateSchedule - Current'[RateScheduleName] column (1 General Service, 1 Irrigation, 1 Large Commercial, 1 Large Commercial Load Control, and 1 Residential & Small Commercial).
When I make a table in my report with 'RateSchedule - Current[UtilityName] and my formula above, I get a count of 10 instead of 5.
 
I've tried changing the options in the "Visualizations" tab to Count, Distinct Count, Don't Summarize etc. and nothing yields the results I want.
 
Please let me know if more info is needed!
 
Thank you!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@rweb95 , Try measure as

calculate(DISTINCTCOUNT('RateSchedule - Current'[RateScheduleName]), filter('RateSchedule - Current', 'RateSchedule - Current'[IsActive] && 'RateSchedule - Current'[IsDefault]
&& isblank('RateSchedule - Current'[CompanyId])))

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Does this work?

=calculate(DISTINCTCOUNT('RateSchedule - Current'[RateScheduleName]), RateSchedule - Current'[IsActive]=TRUE(),'RateSchedule - Current'[IsDefault]=TRUE(),'RateSchedule - Current'[CompanyId]=BLANK())


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@rweb95 , Try measure as

calculate(DISTINCTCOUNT('RateSchedule - Current'[RateScheduleName]), filter('RateSchedule - Current', 'RateSchedule - Current'[IsActive] && 'RateSchedule - Current'[IsDefault]
&& isblank('RateSchedule - Current'[CompanyId])))

Wonderful! It did work. Thank you for your help!

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.