Difficulty Displaying Email Frequency Counts in Matrix Form

[Anonymous]

Hi,

I am creating an email frequency count dashboard by country, where I have used two calculated measures:

Total_Email_Count:

Total_Email_Count = CALCULATE(DISTINCTCOUNT(Query1[Activity_id]),

    ALLEXCEPT(Query1,

        Query1[Email_Send_Date].[Year],

        Query1[Email_Send_Date].[Month],

        Query1[Email_Address],

        Query1[Team_Name],

        Query1[Leading_Theme],

        Query1[Email_Type],

        Query1[Country]

    )

)

This measure counts the unique emails received by a person.

Email_Frequency_Count:

Email_Frequency_Count =

SWITCH (

    TRUE (),

    [Total_Email_Count] >= 1 && [Total_Email_Count] <= 5, "1 - 5",

    [Total_Email_Count] >= 6 && [Total_Email_Count] <= 10, "6 - 10",

    [Total_Email_Count] >= 11 && [Total_Email_Count] <= 15, "11 - 15",

    [Total_Email_Count] >= 16 && [Total_Email_Count] <= 20, "16 - 20",

    [Total_Email_Count] >= 21 && [Total_Email_Count] <= 25, "21 - 25",

    [Total_Email_Count] >= 26 && [Total_Email_Count] <= 30, "26 - 30",

    [Total_Email_Count] >= 31 && [Total_Email_Count] <= 35, "31 - 35",

    [Total_Email_Count] >= 36 && [Total_Email_Count] <= 40, "36 - 40",

    [Total_Email_Count] >= 41 && [Total_Email_Count] <= 45, "41 - 45",

    [Total_Email_Count] > 45, "More than 45",

    BLANK ()

)

This measure categorizes email counts into frequency brackets.

Now, my client has asked me to present this dashboard in matrix form, where the rows of the matrix table should display countries, the columns should show Email Frequency Count, and the values section should count unique email addresses. However, I'm facing an issue with displaying Email Frequency Count in the column field. Can you suggest an alternative approach or solution? Your help would be greatly appreciated.

Let me know if you need any further assistance or clarification!

Regards,

Digant

[rajendraongole1]

Hi @Anonymous - create calculated column to get the total email count

Total_Email_Count_Column =
CALCULATE(
DISTINCTCOUNT(Query1[Activity_id]),
ALLEXCEPT(
Query1,
Query1[Email_Send_Date].[Year],
Query1[Email_Send_Date].[Month],
Query1[Email_Address],
Query1[Team_Name],
Query1[Leading_Theme],
Query1[Email_Type],
Query1[Country]
)
)

create another column for the email frequency count based

Email_Frequency_Count_Column =
SWITCH(
TRUE(),
Query1[Total_Email_Count_Column] >= 1 && Query1[Total_Email_Count_Column] <= 5, "1 - 5",
Query1[Total_Email_Count_Column] >= 6 && Query1[Total_Email_Count_Column] <= 10, "6 - 10",
Query1[Total_Email_Count_Column] >= 11 && Query1[Total_Email_Count_Column] <= 15, "11 - 15",
Query1[Total_Email_Count_Column] >= 16 && Query1[Total_Email_Count_Column] <= 20, "16 - 20",
Query1[Total_Email_Count_Column] >= 21 && Query1[Total_Email_Count_Column] <= 25, "21 - 25",
Query1[Total_Email_Count_Column] >= 26 && Query1[Total_Email_Count_Column] <= 30, "26 - 30",
Query1[Total_Email_Count_Column] >= 31 && Query1[Total_Email_Count_Column] <= 35, "31 - 35",
Query1[Total_Email_Count_Column] >= 36 && Query1[Total_Email_Count_Column] <= 40, "36 - 40",
Query1[Total_Email_Count_Column] >= 41 && Query1[Total_Email_Count_Column] <= 45, "41 - 45",
Query1[Total_Email_Count_Column] > 45, "More than 45",
BLANK()
)

Now create a measure to find the unique email address as below:

Unique_Email_Count = DISTINCTCOUNT(Query1[Email_Address])

Use these calculations in your visual. Hope it helps.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Hi @rajendraongole1 ,

Thank you for your response!

Currently, I'm using four different slicers in my dashboard, which is why I'm avoiding DAX caclculations in calculated column. Could you please suggest other possible solutions

Regards,

Digant

[Ashish_Mathur · Accepted solution]

Hi,

In the attached file, you will find a solution using measures.

Hope this helps.

[Anonymous]

Hi, @Anonymous 

I think the method he gives is correct, the columns in the matrix have to be computed columns for it to work, if you avoid creating computed columns then you can use a Table in order to create a similar visual object.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum