Condition in Distinct Count ( Measure)

[Kavya123]

Hi Everyone,

I need to get distinct count of organization based on certain conditions.  I am getting below error in the measure. Please help me to correct this.

Measure 5 =

var test1=CALCULATE(AVERAGE(PROFILERTABLENEWSUBMITTED[NEWScore]) , PROFILERTABLENEWSUBMITTED[Answer_Flag]=1, FILTER(PROFILERTABLENEWSUBMITTED, PROFILERTABLENEWSUBMITTED[PDataPoint]=1208 ))

var test2= FLOOR(CALCULATE(AVERAGE( PROFILERTABLENEWSUBMITTED[NEWScore]), ALL(PROFILERTABLENEWSUBMITTED[POrganization]) , PROFILERTABLENEWSUBMITTED[Answer_Flag]=1 , PROFILERTABLENEWSUBMITTED[PDataPoint]=1208),0.5)

return

CALCULATE(DISTINCTCOUNT( PROFILERTABLENEWSUBMITTED[POrganization] ) , test1<test2)

[v-chuncz-msft · Accepted solution]

@Kavya123 ,

You may add FILTER Function to the formula.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

[Kavya123]

Thanks for the reply.. I was able to resolve.

CALCULATE( COUNTROWS (

FILTER (

VALUES( PROFILERTABLENEWSUBMITTED[POrganization]),

if ( and ([Measure 2]>[Measure 4], [Measure 3]<[Measure 4]), 1,0) )

) , FILTER( PROFILERTABLENEWSUBMITTED, PROFILERTABLENEWSUBMITTED[PDataPoint]=1208 || PROFILERTABLENEWSUBMITTED[PDataPoint]=1207))