Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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    
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    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.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    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.