Date Filtering Issue MANY to Many Relationship in (dev_con_hcp_Universe table)

[Invesco]

Hi Experts

When using the day_date column asa filter in my Power BI i am unable to filter the number of consent in my matrix table based on Product. Product is in Dev_con_hcp_universe.

02 | Calendar Table link to Dev_con_consent via Day_id to Capture_day_id

Dev-con_consent links to Dev_con_hcp_universe via Crmf_hcp_id to Crrmf_hcp_id (many-to-many)

Cannot see my error.

my DAX measure is

Total Consent =

VAR EndConsentdate = MAX('02 | Calendar'[day_date])

VAR StartConsentdate = MIN('02 | Calendar'[day_date])

VAR Result =

    CALCULATE(

            [Number of People with Consent],

            REMOVEFILTERS('02 | Calendar'),

            dev_con_consent[capture_day_date] <= EndConsentdate,

            dev_con_consent[expiration_day_date] > StartConsentdate

        ||

        ISBLANK(dev_con_consent[expiration_day_date])

    )

Return

Result    

[v-jingzhang · Accepted solution]

Hi @Invesco 

What is the cross-filter direction between Dev-con_consent and Dev_con_hcp_universe? For a many-to-many relationship, it will be Both by default. But since your Product is in Dev_con_hcp_universe and you want to use Product to filter Dev-con_consent table, you can modify its cross-filter direction to Single (Dev_con_hcp_universe filters Dev-con_consent). This may make a difference. 

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.