Forum Discussion
Column to toggle between single or multiple categories
I want to create a column that allows me to toggle between emails associated with single or multiple clients (categories). I have a stacked column chart with the following fields.
Axis: 'Email'
Legend: 'Client'
Values: 'Request Count'
Currently, the chart is plotting counts by 'Email' for all 'Clients' including those which just a single client.
Ideally, I'd like to filter to only the emails which have more than 1 client associated with them.
Below is an example (all emails are encrypted). The emails highlighted yellow would remain, as they have counts for multiple clients. However, I'd like to filter out the emails with the red line through them, as they are associated with just a single client (single-color bars)
Any assistance in writing a DAX formula to achieve this filter would be much appreciated! Please let me know if you need me to provide any further details.
11 Replies
Hello niko18033 ,
May I request you to share the sample table structure from which you are plotting the visual. It will help in writing appropriate DAX.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on TwitterHi Vivek - Thanks for your quick reply. Below is a sample export of the data. The emails which are essentially duplicates in this table (have a row for more than one client) are the ones I want to keep.
Request Count is a measure. = DISTINCTCOUNT(requests)
Email Client Request Count [email protected] Client A 2 [email protected] Client A 2 [email protected] Client A 3 [email protected] Client B 3 [email protected] Client C 2 [email protected] Client D 9 [email protected] Client C 2 [email protected] Client A 3 [email protected] Client C 3 [email protected] Client A 2 [email protected] Client A 5 [email protected] Client A 2 [email protected] Client A 2 [email protected] Client A 3 [email protected] Client D 2 [email protected] Client D 2 [email protected] Client A 2 [email protected] Client A 2 [email protected] Client B 2 [email protected] Client A 2 You may try this as a measure:
Multiple Clients = //Create a summary table VAR _StepTable = SUMMARIZE( 'Email Table', 'Email Table'[Email], "Client Count", DISTINCTCOUNT('Email Table'[Client]), "Request Count", SUM('Email Table'[Request Count]) ) //Filter out records with single client VAR _Filter = FILTER(_StepTable, [Client Count] > 1 ) //Get the sum of request count for emails with multiple clients VAR _SumOfRequest = SUMX(_Filter,[Request Count]) RETURN _SumOfRequestCheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
niko18033 , Try measure like, Assumed you already have measure Request Count
sumx(filter(summarize(Table, Table[Email],"_1",count(Table[Client]),"_1",[Request Count]),[_1]>1),[Request Count])
or
sumx(filter(summarize(Table, Table[Email],"_1",distinctcount(Table[Client]),"_1",[Request Count]),[_1]>1),[Request Count])
- You have to have a dimension 'Emails' and one of the attributes must be the number of clients where the values would be 'Single' and 'Multiple'. You'll then place the EmailID on the axis and filter by one of the values. The chart will then show on the x-axis only the filtered emails. You can't achieve what you want through a measure.
