Forum Discussion

ringovski's avatar
ringovski
Helper II
4 years ago

Countrows from Sumx

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.

 

 

DAX Commands and Tips

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      ringovski
      Helper II
      4 years ago

      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.

  • m3tr01d's avatar
    m3tr01d
    Continued Contributor
    4 years ago

    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)