Removefilters with active relationship not working with Date

[daicaboy]

Hello everyone,

I have a problem when use removefilter. The total is right but some row value don't show in the report. I had a Table 1 with Product and ID have active relationship with ID in Table 2. And the Calendar Table have active relationship with Fix Date in Table 2. I want to count Product fix within a year and have Date warranty less than 3 year, and both date from the  Year and Month Visual with date in Calendar Table.

Table1:

ID	Product
RNA	Product A
SSP	Product A
ATS	Product B
CTS	Product C
RSF	Product C

Table 2:

ID	Fix Date	Warranty Date
RNA	31/03/2025	02/05/2025
RNA	15/03/2025	02/05/2025
RNA	01/02/2025	02/05/2025
RNA	14/12/2024	02/05/2025
RNA	03/05/2024	02/05/2025
SSP	03/05/2022	01/03/2021
SSP	12/09/2024	01/03/2021
SSP	24/08/2024	01/03/2021
CTS	03/04/2025	02/05/2019
CTS	01/01/2024	02/05/2019
CTS	05/06/2024	02/05/2019
CTS	28/04/2025	02/05/2019
CTS	14/06/2021	02/05/2019
RSF	01/05/2023	05/04/2022

The result of Total is right, but it didn't show all the product. Because my data with over 15 million row so I just show the example of my result.
Here the result I got. As you can see the Total show all the Qty but Row only show Product A and B, Product C is missing.

Product	Qty
Product A	1
Product B	3
Total	10

The Right result should be:

Product	Qty
Product A	1
Product B	3
Product B	6
Total	10

When I tried Inactive relationship between Calendar and Table2 and Delete REMOVEFILTERS It's work fine and show the Product C.

Here my DAX:

Product Count = 
VAR DateVisual = MAX('Calendar'[Date])
VAR DateCount = EOMONTH(DateVisual,-12)+1
VAR DateShow = CALCULATE(MAX('Table2'[Fix Date]))
VAR ProductCount =
    CALCULATE(
        DISTINCTCOUNT('Table2'[ID],
        FILTER(
            ALL('Table2'[Fix Date],'Table2'[Warranty Date]),
            'Table2'[Fix Date] >= DateCount &&
            'Table2'[Fix Date] <= DateVisual &&
            NOT ISBLANK('Table2'[Warranty Date])&&
            'Table2'[Warranty Date] >= EOMONTH(DateVisual, -36) + 1
        ),
        REMOVEFILTERS('Calendar')
    )
RETURN
IF(DateVisual <= DateShow, ProductCount, BLANK())

My data are heavy, I need active relationship and use one Calendar Table to calculate other, so I can't create a new Calendar or add USERELATIONSHIP.

Thanks.

Thanks.

[v-karpurapud]

Hi @daicaboy 

We are following up to see if your query has been resolved. Please feel free to contact us if you have any further questions.  Could you please confirm if your query has been resolved? If it has, kindly mark the helpful response and accept it as the solution. This will help other community members resolve similar issues more efficiently. If not, please provide detailed information so we can assist you better.

Thank You.

[daicaboy]

Hi,
I need to count how many product satisfy 2 conditions: have fix date less than a year from visual Calendar and waranty date less than 3 year from the visual. Some month work fine, but some month it missing some product. Like It's show Total 10, but only show Product A: 1, Product B: 3, missing Product C. And when I'm inactive and don't use REMOVEFILTERS it show product C: 6. So How to show right Result Product C: 6 when I active the relationship.
Example issue:

But the right result should be 

Thanks.

[FBergamaschi]

I understand but in the data you provided there is no product B, can you please check my results and tell me which is incorrect? I cannot get your result if I have no product B

[daicaboy]

Oh so sorry, because big data so I only copy some of them for easy to imagine. As I said I dont understand why Total is 10 but It only show product A: 1 and B: 3. I can make sure that my data not missing product C because when I tried inactive it and delete REMOVEFILTERS, it's will show product C.

[FBergamaschi]

Ok but with the data you provided, are my results correct from your point of view? otherwise we never get to a point

Best

FB

[daicaboy]

Yes, It is right. Thank

[FBergamaschi]

This is my code, whose results you said are correct based on the data you provided

Product Count =

VAR DateVisual = MAX('Calendar'[Date])

VAR DateCount = EOMONTH(DateVisual,-12)+1

VAR DateShow = MAX('Fact'[Fix Date])

VAR ProductCount =

    CALCULATE(

        DISTINCTCOUNT('Fact'[ID]),

            'Calendar'[Date] >= DateCount,

            'Calendar'[Date] <= DateVisual,

            NOT ISBLANK(Fact[WarrantyDate]),

            Fact[WarrantyDate] >= EOMONTH(DateVisual, -36) + 1

    )

RETURN

ProductCount

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[daicaboy · Accepted solution]

Thank you,
I've find down the problem come from, it's from the DateShow. Somehow it's limited the day so I add removefilters in Dateshow, and It's ok now.

[v-karpurapud]

Hi @daicaboy 

We are pleased to hear that you have found the cause and resolved the issue. Thanks for sharing the details here.  Please mark as 'Accept as solution' to help others with similar problems find it easily. 

Thank you.

[FBergamaschi]

Hi, attached the result I have based on your data

To my view the result is correct, there is no product B in the data you provided and with the warranty dates in the tabe it seems to me it works

Can you tell which result is wrong and what shoud that be?

Best