Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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])
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
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)
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 |
[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...
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.
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
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."
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
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.