Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Countrows with multiple conditions

I have a measure that counts the number of items based on the Origin and Acceptance Datetime:

 

TotalACCEPtoRFIDINACCEPItems = CALCULATE(COUNTROWS('Scan Event DataQ'),FILTER('Scan Event DataQ','Scan Event DataQ'[OriginMailCentre]="Auckland"&&'Scan Event DataQ'[ACCEPTANCE DATETIME]<>BLANK()))

 

However I need to modify it to include items from additional Origins and their Acceptance Datetimes:

if [OriginMailCentre]=Christchurch then [CHM RFID IN ACCEP DATETIME]<>BLANK

if [OriginMailCentre]=Manawatu then [MTM RFID IN ACCEP DATETIME]<>BLANK

if [OriginMailCentre]=Auckland then [AKM RFID IN ACCEP DATETIME]<>BLANK

Hope that makes sense. Appreciate any help, thanks.

  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous  , add

    && Switch ( True () ,
    [OriginMailCentre]="Christchurch" && [CHM RFID IN ACCEP DATETIME]<>BLANK,1 ,
    [OriginMailCentre]="Manawatu" && [MTM RFID IN ACCEP DATETIME]<>BLANK,1 ,
    [OriginMailCentre]="Auckland" && [AKM RFID IN ACCEP DATETIME]<>BLANK,1 ,0
    ) =1

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous  , add

    && Switch ( True () ,
    [OriginMailCentre]="Christchurch" && [CHM RFID IN ACCEP DATETIME]<>BLANK,1 ,
    [OriginMailCentre]="Manawatu" && [MTM RFID IN ACCEP DATETIME]<>BLANK,1 ,
    [OriginMailCentre]="Auckland" && [AKM RFID IN ACCEP DATETIME]<>BLANK,1 ,0
    ) =1

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak, I get syntax error (the syntax for ',' is incorrect) when adding your measure:

       

      TotalACCEPtoRFIDINACCEPItems = CALCULATE(COUNTROWS('Scan Event DataQ'),FILTER('Scan Event DataQ',[ACCEPTANCE DATETIME]<>BLANK()&&Switch(True(),
[OriginMailCentre]="Christchurch" && [CHM RFID IN ACCEP DATETIME]<>BLANK,1,
[OriginMailCentre]="Manawatu" && [MTM RFID IN ACCEP DATETIME]<>BLANK,1,
[OriginMailCentre]="Auckland" && [AKM RFID IN ACCEP DATETIME]<>BLANK,1,0
 )=1))

       

       

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Anonymous , you might want to try amending your measure this way,

    TotalACCEPtoRFIDINACCEPItems =
CALCULATE (
    COUNTROWS ( 'Scan Event DataQ' ),
    FILTER (
        'Scan Event DataQ',
        'Scan Event DataQ'[OriginMailCentre] = "Auckland"
            && 'Scan Event DataQ'[ACCEPTANCE DATETIME] <> BLANK ()
            || 'Scan Event DataQ'[OriginMailCentre]
            IN { "Christchurch", "Manawatu", "Auckland" }
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      CNENFRNLthank you for your suggestion however your measure does not include the Acceptance Datetime columns i.e. [CHM RFID IN ACCEP DATETIME], [MTM RFID IN ACCEP DATETIME], [AKM RFID IN ACCEP DATETIME]