Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Measure with dynamic date slicer.

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!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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?