Forum Discussion
How to remove duplicates count
Hi, I have created some measures in this sample pbix file. If a product is valid for both "Contract" & Warranty" in the same period, how to count only "Warranty" for that product. (E.g. Product A in 2022-02 and Product D in 2022-03) If a product is valid for both "Rental" & "Warranty" in the same month, how to count only "Warranty" for that product. (E.g. Product B in 2022-02)
Besides, the sum for T&M is incorrect? How to fix it?
Hi PBI_newuser ,
Please refer to my pbix file to see if it helps you.
Create measures.
contract_ = var answer_=CALCULATE(DISTINCTCOUNT('Product'[Product]), filter('Product', 'Product'[Installed Date]<= max('Calendar'[Date]) && 'Product'[Contract Start Date] <= max('Calendar'[Date]) && 'Product'[Contract End Date] >= min('Calendar'[Date]) && 'Product'[Term]="Purchased" ) ) return IF(answer_=[Warranty],BLANK(),answer_)_contract_ = var _a = 'Product'[contract_] var _b = SUMMARIZE('Product','Product'[Product],"aaa",'Product'[contract_]) return IF(HASONEVALUE('Product'[Product]),_a,SUMX(_b,[aaa]))T&M = CALCULATE(DISTINCTCOUNT('Product'[Product]), filter(ALL('Product'), ('Product'[Installed Date] <= max('Calendar'[Date]) && 'Product'[Contract Start Date] = BLANK()) || ('Product'[Contract Start Date]<>BLANK() && Max('Product'[Contract End Date]) < min('Calendar'[Date]))))Measure = var _a = [T&M] var _b = SUMMARIZE('Product','Product'[Product],"aaa",[T&M]) return IF(HASONEVALUE('Product'[Product]),_a,SUMX(_b,[aaa]))If I have misunderstood your meaning, please provide your desired output with a screenshoot.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi PBI_newuser ,
Please refer to my pbix file to see if it helps you.
Create measures.
contract_ = var answer_=CALCULATE(DISTINCTCOUNT('Product'[Product]), filter('Product', 'Product'[Installed Date]<= max('Calendar'[Date]) && 'Product'[Contract Start Date] <= max('Calendar'[Date]) && 'Product'[Contract End Date] >= min('Calendar'[Date]) && 'Product'[Term]="Purchased" ) ) return IF(answer_=[Warranty],BLANK(),answer_)_contract_ = var _a = 'Product'[contract_] var _b = SUMMARIZE('Product','Product'[Product],"aaa",'Product'[contract_]) return IF(HASONEVALUE('Product'[Product]),_a,SUMX(_b,[aaa]))T&M = CALCULATE(DISTINCTCOUNT('Product'[Product]), filter(ALL('Product'), ('Product'[Installed Date] <= max('Calendar'[Date]) && 'Product'[Contract Start Date] = BLANK()) || ('Product'[Contract Start Date]<>BLANK() && Max('Product'[Contract End Date]) < min('Calendar'[Date]))))Measure = var _a = [T&M] var _b = SUMMARIZE('Product','Product'[Product],"aaa",[T&M]) return IF(HASONEVALUE('Product'[Product]),_a,SUMX(_b,[aaa]))If I have misunderstood your meaning, please provide your desired output with a screenshoot.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi amitchandak , please try this link to access to the file.
https://drive.google.com/file/d/1cucl065ufUTpb9TalLiHBnI4fnAkcSmm/view?usp=sharing
Hi PBI_newuser ,
Please refer to my pbix file to see if it helps you.
Please modify the formula of contract and TM.
contract_ = VAR answer_ = CALCULATE ( DISTINCTCOUNT ( 'Product'[Product] ), FILTER ( 'Product', 'Product'[Installed Date] <= MAX ( 'Calendar'[Date] ) && 'Product'[Contract Start Date] <= MAX ( 'Calendar'[Date] ) && 'Product'[Contract End Date] >= MIN ( 'Calendar'[Date] ) && 'Product'[Term] = "Purchased" ) ) RETURN IF ( answer_ = [Warranty], BLANK (), answer_ )T&M = CALCULATE ( DISTINCTCOUNT ( 'Product'[Product] ), FILTER ( ALL ( 'Product' ), ( 'Product'[Installed Date] <= MAX ( 'Calendar'[Date] ) && 'Product'[Contract Start Date] = BLANK () ) || ( 'Product'[Contract Start Date] <> BLANK () && MAX ( 'Product'[Contract End Date] ) < MIN ( 'Calendar'[Date] ) ) ) )If I have misunderstood your meaning, please provide your desired output with a screenshoot.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , thank you for your help! The measure "contract_" looks good but the sum is incorrect. The sum for contract_ for 2022-02 shows 2 but it should be 1.
For T&M, I want to count the product if they have expired contract. Below measure seems correct but the sum is incorrect. How to fix it?
T&M = CALCULATE(DISTINCTCOUNT('Product'[Product]),
filter('Product',
('Product'[Installed Date] <= max('Calendar'[Date]) && 'Product'[Contract Start Date] = BLANK()) ||
('Product'[Contract Start Date]<>BLANK() && Max('Product'[Contract End Date]) < min('Calendar'[Date]))))
Hi PBI_newuser ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
If I have misunderstood your meaning, please provide your desired output with a screenshoot.
Best Regards
Community Support Team _ Polly
