Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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
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" }
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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]
@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
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))