How to remove duplicates count

[PBI_newuser]

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?

[v-rongtiep-msft]

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

[v-rongtiep-msft · Accepted solution]

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.

[v-rongtiep-msft]

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.

[PBI_newuser]

Hi @v-rongtiep-msft , 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]))))

[amitchandak]

@PBI_newuser , Please provide access to the file

refer why grand total is wrong

https://www.youtube.com/watch?v=ufHOOLdi_jk

[PBI_newuser]

Hi @amitchandak , please try this link to access to the file.

https://drive.google.com/file/d/1cucl065ufUTpb9TalLiHBnI4fnAkcSmm/view?usp=sharing