topic Re: Column to toggle between single or multiple categories in DAX Commands and Tips

Column to toggle between single or multiple categories

niko18033 — Tue, 18 Aug 2020 23:16:54 GMT

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.

Re: Column to toggle between single or multiple categories

vivran22 — Wed, 19 Aug 2020 02:41:32 GMT

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

Re: Column to toggle between single or multiple categories

amitchandak — Wed, 19 Aug 2020 04:24:29 GMT

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

sumx(filter(summarize(Table, Table[Email],"_1",distinctcount(Table[Client]),"_1",[Request Count]),[_1]>1),[Request Count])

Re: Column to toggle between single or multiple categories

Anonymous — Wed, 19 Aug 2020 10:35:55 GMT

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.

Re: Column to toggle between single or multiple categories

niko18033 — Wed, 19 Aug 2020 15:48:04 GMT

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

Re: Column to toggle between single or multiple categories

Anonymous — Wed, 19 Aug 2020 16:22:57 GMT

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.

Re: Column to toggle between single or multiple categories

niko18033 — Wed, 19 Aug 2020 17:02:20 GMT

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.

Re: Column to toggle between single or multiple categories

vivran22 — Wed, 19 Aug 2020 17:05:36 GMT

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

Re: Column to toggle between single or multiple categories

niko18033 — Wed, 19 Aug 2020 18:44:59 GMT

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")

Re: Column to toggle between single or multiple categories

vivran22 — Thu, 20 Aug 2020 02:53:57 GMT

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

Re: Column to toggle between single or multiple categories

Anonymous — Thu, 20 Aug 2020 08:18:35 GMT

[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...

Re: Column to toggle between single or multiple categories

Anonymous — Thu, 20 Aug 2020 08:26:38 GMT

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.