Forum Discussion

rweb95's avatar
rweb95
Frequent Visitor
5 years ago
Solved

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!

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    rweb95 , Try measure as

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    rweb95 , Try measure as

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

    • rweb95's avatar
      rweb95
      Frequent Visitor
      5 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Does this work?

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