topic Countrows with multiple conditions in DAX Commands and Tips

Countrows with multiple conditions

Anonymous — Thu, 19 Nov 2020 08:59:34 GMT

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.

Re: Countrows with multiple conditions

amitchandak — Thu, 19 Nov 2020 09:23:31 GMT

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

Re: Countrows with multiple conditions

CNENFRNL — Thu, 19 Nov 2020 15:20:05 GMT

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

Re: Countrows with multiple conditions

Anonymous — Thu, 19 Nov 2020 18:32:31 GMT

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

Re: Countrows with multiple conditions

Anonymous — Thu, 19 Nov 2020 18:34:37 GMT

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]