Column Distinct Count with Several Conditions

[rweb95]

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:

UtilityName	RateScheduleName	IsDefault	IsActive	CompanyId
F Electric	Husker		False	11
F Electric	General Service	True	True
F Electric	General Service	True	True
F Electric	Glacial Energy	False	True	12
F Electric	Irrigation	True	True
F Electric	Irrigation	True	True
F Electric	Large Commercial	True	True
F Electric	Large Commercial	True	True
F Electric	Large Commercial Load Control	True	True
F Electric	Large Commercial Load Control	True	True
F Electric	Residential & Small Business	True	True
F Electric	Residential & Small Business	True	True

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!

[Ashish_Mathur]

Hi,

Does this work?

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

[amitchandak · Accepted solution]

@rweb95 , Try measure as

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

[rweb95]

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