Forum Discussion
kotarosai
Helper II
5 years agoDISTINCTCOUNT with multiple filter criteria
Hello, I am having an issue with a measure / formula. One works as intended when using an OR logic but does not work as intended with an AND. Both formulas are below and the measure is shown in the matrix table. Any support is appreciated, thanks!
WORKS:
Retained Accounts Past QTR = CALCULATE(
DISTINCTCOUNT('Sales Result'[Account]),
FILTER('Sales Result',[Accounts Ordered Past QTR] > 0 || [Accounts Ordered 2 QTRs Ago] > 0))
DOES NOT WORK:
Retained Accounts Past QTR = CALCULATE(
DISTINCTCOUNT('Sales Result'[Account]),
FILTER('Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))
Accounts Ordered Past QTR = CALCULATE(
DISTINCTCOUNT('Sales Result'[Account]),
FILTER('Sales Result','Sales Result'[Sales Past QTR] > 0))Accounts Ordered 2 QTRs Ago = CALCULATE(
DISTINCTCOUNT('Sales Result'[Account]),
FILTER('Sales Result','Sales Result'[Sales 2 QTRs Ago] > 0))Sales Past QTR =
VAR CurrentDate = [Latest Date Loaded]
RETURN
CALCULATE(
SUM('Sales Result'[Net_Sales_Units__c]),
FILTER(
ALL('Date Table'),
AND('Date Table'[Date]>=IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-1)*3+1,1),
Date(1900,1,1)
),
'Date Table'[Date]<IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)+0)*3+1,1),
Date(1900,1,1)
)
)
)
)Sales 2 QTRs Ago =
VAR CurrentDate = [Latest Date Loaded]
RETURN
CALCULATE(
SUM('Sales Result'[Net_Sales_Units__c]),
FILTER(
ALL('Date Table'),
AND('Date Table'[Date]>=IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-2)*3+1,1),
Date(1900,1,1)
),
'Date Table'[Date]<IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-1)*3+1,1),
Date(1900,1,1)
)
)
)
)
kotarosai , Try like and check
Retained Accounts Past QTR = Countrows(
filter(values('Sales Result'[Account]),'Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))
2 Replies
- amitchandak
Super User
kotarosai , Try like and check
Retained Accounts Past QTR = Countrows(
filter(values('Sales Result'[Account]),'Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))- kotarosai
Helper II
That worked great, thanks so much amitchandak!