cancel
Showing results for
Did you mean:
Post Patron

## Count result of measure with condition

For example i have same tables

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
))

1 ACCEPTED SOLUTION
Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors