Column to toggle between single or multiple categories

[niko18033]

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.

[Anonymous]

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.

[amitchandak]

@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])

[vivran22]

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 Twitter

[niko18033]

Hi 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
00539XAKOIOYVFPFPE@KNDUB.JAB	Client A		2
014RMOIJIJ@FEPGS.PIH	Client A		2
0174@YGH.UHY	Client A		3
0174@YGH.UHY	Client B		3
03UFWTEN@RPCFF.EEU	Client C		2
0598JN@ZXOPY.CUD	Client D		9
07FBTAUYMNE@AAOXR.DUG	Client C		2
07LANWWAN@SNWVM.AAD	Client A		3
07LANWWAN@SNWVM.AAD	Client C		3
08VMURWVXTAALNQFM@IYCPY.LHK	Client A		2
0CX7YA@AQSJU.TKG	Client A		5
0KHXZYFWILP@VDLDK.KXD	Client A		2
0ZRESSS@DXX.OIH	Client A		2
1268EO@VBZAE.CBT	Client A		3
1268EO@VBZAE.CBT	Client D		2
142.LAHYEYNZMOTAR@JIAJK.JMW	Client D		2
1628UUEBQ@ZRPTX.OIC	Client A		2
171YVA@FRTPW.YYW	Client A		2
178N369@APXQS.FUW	Client B		2
178N369@APXQS.FUW	Client A		2

[Anonymous]

[Email Type] = // calculated column
var __email = T[Email] // T is the table
var __howMany =
    countrows(
        filter(
            T,
            T[Email] = __email
        )
    )
return
    If( __howMany > 1, "Multiple", "Single" )

Here is your attribute...

[Anonymous]

By the way, creating a measure - as vivran22 suggests - will be of no use to you in this regard. Secondly, this calculation should ideally be performed in Power Query since this piece of Power BI has been designed to efficiently deal with such problems through the M language. On top of that, if you do it in DAX, as I did above, the compression rate will not be optimal (but it might suffice). Please note that all preprocessing should be always performed in Power Query, not in DAX. DAX is a Data Analysis eXpressions language, not a data mashup language like M.

The fact that something is doable does not necessarily mean it should be done.

[vivran22]

@niko18033 

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
_SumOfRequest

Cheers!
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]

@vivran22 

Thanks, Vivek. I tried that, but it seems to be extremely processor/RAM intensive. It ran for almost an hour but failed due a lack of system resources. I tried a workaround, by creating a column rather than a measure. This way, the user also has the option whethere or not to filter out the "single" client emails. I'm trying to do a column that yields two values: "Single" or "Multiple", referring to the number of clients associated with the email.

However, I'm getting this error. Am I missing something?

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Multiple Clients? =

VAR StepTable =

SUMMARIZE(

'Table1',

Table1[Email],

"Client Count", DISTINCTCOUNT('Table1'[CLIENT_NAME]),

"Request Count", [Request Count])

//Filter out records with single client

RETURN

IF(FILTER(StepTable, [Client Count] > 1), "Multiple", "Single")

[vivran22]

@niko18033 

What is your data source, Excel, SQL? And how many records in total (estimate)?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

As I said above, you have to add an attribute to your table that for each email will tell you whether it's a 'single' one or 'multiple' one. Then and only then will you be able to slice via this attribute and the x-axis of your visual will adjust accordingly.

[niko18033]

Thanks for the follow-up, @Anonymous . What would be the best way to create such an attribute? Through Power Query Editor and doing a 'Group By' aggregation? Or a conditional column? I was thinking of just writing a DAX column, but am unsure how to define this.