Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Difficulty Displaying Email Frequency Counts in Matrix Form

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]),   ...
General Comment
Need Help
Tips and Tricks
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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