Need help to apply count function on a measure

[Anonymous]

Hi Team,

I have to apply count function on a measure column, below is the logic used to create a measure column and please note it is mandatory to have the logic implemented in measure column only, because i'm also using another measure column in the below logic  (TargetCritical,TargetMedium ..etc) for a comparing target values which are dynamic values.

Target_hrs_Exceded_1.10_SUMX =

VAR var1 = SUMX(FILTER('Case','Case'[Priority] In {"critical","criticalEscalated"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate],IF(YEAR('Case'[CaseClosedDateTime])>2000, 'Case'[CaseClosedDateTime],UTCNOW()),Hour))

VAR var2 = IF(var1>[TargetCritical],var1)

VAR var3 = SUMX(FILTER('Case','Case'[Priority] In {"high"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate],IF(YEAR('Case'[CaseClosedDateTime])>2000, 'Case'[CaseClosedDateTime],UTCNOW()),Hour))

VAR var4 = IF(var3>[TargetHigh],var3)

VAR var5 = SUMX(FILTER('Case','Case'[Priority] In {"medium"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate],IF(YEAR('Case'[CaseClosedDateTime])>2000, 'Case'[CaseClosedDateTime],UTCNOW()),Hour))

VAR var6 = IF(var5>[TargetMedium],var5)

VAR var7 = SUMX(FILTER('Case','Case'[Priority] In {"low"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate],IF(YEAR('Case'[CaseClosedDateTime])>2000, 'Case'[CaseClosedDateTime],UTCNOW()),Hour))

VAR var8 = IF(var7>[TargetLow],var7)

VAR var9 = SUMX(FILTER('Case','Case'[Priority] In {"declined","noAnswer","noSymptom"} && 'Case'[CaseStatus] In {"open"}),DATEDIFF('Case'[CaseCreatedDate],IF(YEAR('Case'[CaseClosedDateTime])>2000, 'Case'[CaseClosedDateTime],UTCNOW()),Hour))

VAR var10 = IF(var9>[TargetOthers],var9)

var var11 = var2+var4+var6+var8+var10

RETURN var11

Thanks,

Venkat

[amitchandak]

@Anonymous , When you count a measure , you need force a level

example

countx(values(Table[case ID]), [Measure])

or

countx(summarize(Table,Table[case ID], Table[Country], "_1", [Measure]),[_1])

[Anonymous]

@amitchandak Do you mean to say that before i apply count function on a measure, i have to make sure the output of measure column must be in the table format or row by row  format.

Please correct me if am wrong.

Thanks for quick response.