cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MgLina
Frequent Visitor

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 1
daxer-almighty
Solution Sage
Solution Sage

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

[# Delay] =
CALCULATE(
    COUNTROWS( T ),
    KEEPFILTERS( T[Status] = "delay" )
)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors