Measure with dynamic date slicer.

[Anonymous]

Hello everyone,

I'm encountering a challenge with a measure in Power BI. Let me provide some context:

1. I have a measure called "webinar_engagement" which aims to count the number of email addresses in the "Merged" table for webinar attendance.

2. There are two relationships involving the "Email Address" column:

   • The first relationship is between "SQL_Dimension" (Dimension table) and "Merged" (Fact table).
   • The second relationship is between "SQL_Dimension" (Dimension table) and "SQL_Database_website" (Fact table).

3. Initially, when I created the measure, it only counted the email addresses existing in the "SQL_Database_website" table, resulting in 54 instead of the expected 160.

4. To resolve this discrepancy, I applied the REMOVEFILTERS() function to the measure, which displayed the correct total of 160.

5. However, the measure lost its dynamic behavior with the date slicer, which is a column from the calendar table. There's a relationship between the calendar table and the "SQL_Database_website" table based on the date column. Unfortunately, I can't establish a relationship between the calendar table and the "Merged" table.

Now, here's what I'm aiming for:

I need the "webinar_engagement" measure to:

• Not filter the "SQL_Database_website" table.
• Remain dynamic with the date slicer.

Below is the current formulation of the measure:
webinar_engagement =
CALCULATE(
DISTINCTCOUNT('Merged'[EmailAddress]),
REMOVEFILTERS(),
'Merged'[New Source] = "Webinar Attendance"
)

Any suggestions on how I can achieve the desired outcome would be greatly appreciated. Thank you!

[Anonymous]

Hi @Anonymous ,

To ensure your measure remains dynamic with the date slicer, you need to maintain the relationship between your calendar table and the "SQL_Database_website" table. This relationship is crucial for the slicer to filter the data based on dates.

Since you want to exclude the "SQL_Database_website" table from being filtered but still want to count distinct email addresses from the "Merged" table, consider using USERELATIONSHIP in combination with REMOVEFILTERS . This allows you to specify which relationships should be active during the calculation of the measure.

The correct measure is:

webinar_engagement =
CALCULATE (
    DISTINCTCOUNT('Merged'[EmailAddress]),
    USERELATIONSHIP('Calendar'[Date], 'SQL_Database_website'[Date]),
    'Merged'[New Source] = "Webinar Attendance",
    REMOVEFILTERS('SQL_Database_website')
)

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Anonymous for your support.
Actullay, I got the number 160 fixed again and not dynmaic with the date slicer.
 but i tried to modify it to the following

webinar_engagement =

CALCULATE (

    DISTINCTCOUNT('Merged'[EmailAddress]),

    USERELATIONSHIP('Calendar_table1'[Date], 'Merged'[Date]),

    'Merged'[New Source] = "Webinar Attendance",

    REMOVEFILTERS('SQL_Dimension')

)

And here I got 143 number and dynmaic with the date slicer, but the number is weired a bit and not correct

what do you think?