Forum Discussion
Disitinct count with condition across column
Hello, Anonymous thanks for your help. Ive tried this but it doesnt work.
Ive also tried to get the expected power readings for each month by creating 3 calculated measures and doing an if statements that looks at the values returned by the calculated measures and returning a 1 for each month where the multiple criterias match and 0 otherwise.
| Months | Jan | Feb | Mar | Apr | May | Jun | Jul |
| Street-Number | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd |
| NG-PHC-21 | 80 | 86 | 82 | 70 | 65 | 80 | 40 |
| NG-PHC-32 | 60 | 70 | 88 | 90 | 80 | 86 | 75 |
| NG-PHC-45 | 80 | 82 | 81 | 70 | 82 | 88 | 89 |
Power_over_80 = CALCULATE(COUNT(Table1[Street Number], FILTER(Table1,[Power Rd] >= 80
(This measure returns a "1" for each street each month whose power readings is >or = 80 and "blank" if otherwise
following this I created the 3 measures below
Current_month = CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd]) >= 80 && Table1[Month] = MONTH(TODAY())))
Prev_month = var current_month = MONTH(TODAY()) return CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd] >= 80 && Table1[Month] = current_month - 1))
Prev_2month = var current_month = MONTH(TODAY()) return CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd] >= 80 && Table1[Month] = current_month - 2))
and then ran an if statement against the 3 measures
Count_last2_months = IF(Table1[Current_month] = 1 && Table1[Prev_month] = 1 && Table1[Prev_2month] = 1, 1,0)
This is the measure i expect should look at the returned value for each month and if it is a "1" for three months in a row it returns "1" on the third month.
but i'm still not getting the result i want.
Somebody help me!!!!!!
Hi Anonymous ,
What do you mean it does not work?
- Anonymous6 years agoNot applicable
Nathaniel_C wrote:Hi Anonymous ,
What do you mean it does not work?
@nathaniel i mean it doesnt work the way i want it.