i don't want aggregated measures and i want unique service no count i.e count distinct...please help

Hi again,

Is this closer to what you're after?

(Note: I've left the grand total in to check it still shows 5785)

Power Factor Rating (v3) =

IF {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}>=0 AND {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}<=.6 THEN 'Critical (< 0.6pf )'

ELSEIF {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}>0.6 AND {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}<=.9 THEN 'Moderate (0.6pf To 0.9pf)'

ELSEIF {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}>0.9 AND {FIXED [Service No], [Interval Timestamp (day)]:MAX([Power Factor])}<=1 THEN 'Normal (<0.9pf To 1.0)'

ELSE 'ERROR'

END

This new version is adapted from your Power Factor Rating (copy) calculation. But in this version I think (hope!) we're saying - get the maximum power factor for each Service No on a given day*, and then use that to put the meter into a power factor rating. That way each Service No counts for one and only one rating. You may need to use MIN not MAX if you want to select the "worst" rating based on the lowest power factor...

* If you don't want to filter by day you can probably get rid of the ", [Interval Timestamp (day)" bit in the calc. I had to add a new calc to get the interval timestamp to drop the times. Interval Timestamp (day) = DATETRUNC('day',[Interval TimeStamp])

To test the above I temporarily dragged Service No onto detail, right clicked and chose to show a highlighter, then highlighted a Service No that I could see had multiple power factors on the day in question ... and checked it only got highlighted in one of the bars. Seemed to work, but do whatever extra testing you need to be sure eh!

Results of testing with new calc:

Previous results - wrong!?

Ta,