Countrows from Sumx

[ringovski]

Hi All,

I conditionaly format a number column if the delivery day exceed the overnight max (agreed SLA) , this is simple enough. Works great highlights the rows which exceed the SLA. Now I need to count them, but I am unable to get this working.

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")

I 've tried using a IF like the intial measure format days but it doesn't work, count days with calculate returns 85 and I'm not sure why and you can see that it should be 8.

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),toll_shipments[sender state]="TAS", Toll_Shipments[Shipment Type = "Overnight")

Thanks for any assistance.

[m3tr01d]

hi @ringovski, 

please, tell us what does toll_shipments data is about. Show us a sample of couple of rows of the table (we need to see the columns Delivery days and Overnight max)

[tamerj1]

Hi @ringovski 

what are you slicing by?

can you please share a screeor you data modrl and your visual blanching out any sensitive data?

[amitchandak]

@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"))

[ringovski]

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.