topic Re: Countrows from Sumx in DAX Commands and Tips

Countrows from Sumx

ringovski — Wed, 16 Mar 2022 03:57:56 GMT

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.

Re: Countrows from Sumx

amitchandak — Wed, 16 Mar 2022 04:01:35 GMT

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

Re: Countrows from Sumx

tamerj1 — Wed, 16 Mar 2022 18:07:40 GMT

Hi ringovski

what are you slicing by?

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

Re: Countrows from Sumx

m3tr01d — Wed, 16 Mar 2022 19:27:44 GMT

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)

Re: Countrows from Sumx

ringovski — Wed, 16 Mar 2022 23:53:21 GMT

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.

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.