Count result of measure with condition

[Digger]

For example i have same tables

https://www.youtube.com/watch?v=8wjYWiZ7gpQ

how to count cumulative requests if cumulative requests sum >=10

I need only count and only in one measure

MeasureCount =

VAR sM = MAX(T1[Date])
VAR fM = MAX(Calendar[Date])
VAR sC = SELECTEDVALUE(T1[Project])

VAR CummulativeSumm =
Calculate(
Sum(T1[number])
,FILTER(ALLSELECTED(T1), fM >= T1[Date] && sM >= T1[Date] && T1[Project] = sC)
,Removefilters(T1[Date])
))

VAR CummulativeCount =
Calculate(
Count(T1[number])
,FILTER(ALLSELECTED(T1), fM >= T1[Date] && sM >= T1[Date] && T1[Project] = sC)
,Removefilters(T1[Date])
))

expected
VAR CummulativeCount =
Calculate(
Count(T1[number])
,FILTER(ALLSELECTED(T1), fM >= T1[Date] && sM >= T1[Date] && T1[Project] = sC)
,Removefilters(T1[Date])
,CummulativeSumm >= 10
))

[MFelix · Accepted solution]

Hi @Digger ,

I see that you have two tables one for the date and another for projects, so I assume that you have a relationship between both tables.

If the relationship exists you don't need to have the two dates on your filter syntax.

What I can understand is that you want to return the values when the cumulative is above 10 so in this case you can make your cumulative like this:

MeasureCumulative = 

Var Cumulativevalue = CALCULATE([# Request],'Calendar'[Date]<= MAX('Calendar'[Date]))

Return
IF( Cumulativevalue >=10, Cumulativevalue)

If you don't want to use the auxiliary table you can created the following measure:

MeasureCumulative = 

Var Cumulativevalue = CALCULATE(DISTINCTCOUNT(Request[Request ID]),'Calendar'[Date]<= MAX('Calendar'[Date]))

Return
IF( Cumulativevalue >=10, Cumulativevalue)

Check PBIX File attach.