Forum Discussion

NaderSaeed's avatar
NaderSaeed
Helper II
6 years ago
Solved

Smart Count If

Hi, I'll just go ahead with an example of what I need to do to illustrate what I want.   Line ID Line's Constant 1 0.23 2 0.15 3 0.325 4 0.14 5 0.314   Line ID Sto...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    NaderSaeed 

    Sheet7 is the table with constant, Shee8 is the table with stop reason.

     

    Result = 
var count_= CALCULATE(COUNTROWS(Sheet8),ALLEXCEPT(Sheet8,Sheet8[Line ID],Sheet8[Stop Reason]))
var constant =  CALCULATE(MAX(Sheet7[Line's Constant]),FILTER(Sheet8,Sheet8[Line ID]=RELATED(Sheet7[Line ID])))
Return count_*constant

     


    Paul Zheng
    Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

NaderSaeed 

Sheet7 is the table with constant, Shee8 is the table with stop reason.

 

Result = 
var count_= CALCULATE(COUNTROWS(Sheet8),ALLEXCEPT(Sheet8,Sheet8[Line ID],Sheet8[Stop Reason]))
var constant =  CALCULATE(MAX(Sheet7[Line's Constant]),FILTER(Sheet8,Sheet8[Line ID]=RELATED(Sheet7[Line ID])))
Return count_*constant

 


Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
 

  • NaderSaeed's avatar
    NaderSaeed
    Helper II
    6 years ago

    Brilliant. Exactly what I wanted!

    Thank you so much Paul. 🙂