cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.