Forum Discussion
Countrows from Sumx
ringovski , Try like
tas 1 count days =
var _delivered = SUMX(toll_shipments, Toll_Shipments[Delivery Days])
var _maxdays = SUMX(Delivery Times Ex Tasmania, Delivery Times Ex Tasmania[Overnight Max])
return
Calculate(countrows(toll_shipments),filter(toll_shipments, toll_shipments[sender state]="TAS", Toll_Shipments[Shipment Type = "Overnight"))
- ringovski4 years agoHelper II
A Small screen shot of the data, its pretty simple pick up parcels from one location by State and ship it to another by State then its either Overnight or Off-Peak (x days).
Count the week days between Pick up & Delivered date.
Delivery Days = if( not(isblank([Pick Up Date])) && not(isblank([Actual Delivered Date])), COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( 'Toll_Shipments'[Pick Up Date], Toll_Shipments[Actual Delivered Date] ), "Is Weekday",WEEKDAY([date],2)<6 ,"Is Holiday", CONTAINS('Holiday Dates','Holiday Dates'[Dates],[Date] ) ), [Is Holiday] = FALSE() && [Is Weekday] = TRUE() ) ),BLANK())Then if the delivery days are higher than the overnight max (SLA number) return red. This works great but now I need to count the number of rows which are red.
tas 1 format days = var _delivered = SUMX(toll_shipments, Toll_Shipments[Delivery Days]) var _maxdays = SUMX(Delivery Times Ex Tasmania, Delivery Times Ex Tasmania[Overnight Max]) return IF(_delivered > _maxdays,"Red","Black")Attempt to count the red rows only, this has a error "True False expression does not specify one column. Each True/False expressions used a table filter expression must refer to exactly one column.
tas 1 count days = var _delivered = SUMX(Toll_Shipments,Toll_Shipments[Delivery Days]) var _maxdays = sumx('Delivery Times Ex Tasmania','Delivery Times Ex Tasmania'[Overnight Max]) return IF(_delivered > _maxdays,Calculate(countrows(toll_shipments),filter(toll_shipments, Toll_Shipments[Sender State] = "TAS" && Toll_Shipments[Shipment Type] = "Overnight"),0))I've tried a few different combinations and haven't got it work yet.
Thanks for any assistance.