Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

If contain two values then return

Hi Forum,

 

I am here again to ask help, 

 

Now i have a table with columns like below:

PartNoStepStatus
AAAStep1In time
AAAStep1Delay
AAAStep2Completed
BBBStep1In time
BBBStep1Completed

 

And i want to count the number of "Delay" and "In time" per PartNo and per Step. But the difficuly is i want: if status for the same PartNo and the same Step contain "Delay", then just count "1" for Delay, and if status for the same PartNo and the same Step contain "Completed" or "In time", then count "1" for In time.

 

For example, for the table i have here, the result of count will be:

PartNoStepCount_DelayCount_InTime
AAAStep11 
AAAStep2 1
BBBStep1 1
    
    

 

I try to use the Measure solution below, but it doesn't work well. And i don't understand why.

 

 

 

I create a measure to return one Status per part

 

StatusPerPart = 
var calc=CALCULATE(DISTINCTCOUNT('Table 2'[PartNo]);
filter('Table 2';
'Table 2'[Status]="Delay"))
Return
If(calc=1;"Delay";"In time")

 

After, i create a new measure to count the Status,

Count_InTime = countrows(filter(Table 2;Table 2[StatusPerPart]="In time"))
Count_Delay = countrows(filter(Table 2;Table 2[StatusPerPart]="Delay"))

 

 

 

Thank you!

1 Reply

  • [# In Time] =
    CALCULATE(
        COUNTROWS( T ),
        KEEPFILTERS( T[Status] = "in time" )
    )
    
    [# Delay] =
    CALCULATE(
        COUNTROWS( T ),
        KEEPFILTERS( T[Status] = "delay" )
    )