Forum Discussion

niko18033's avatar
niko18033
Helper I
6 years ago

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' ...
niko18033's avatar
niko18033
Helper I
6 years ago

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)

 

EmailClient 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

 

vivran22's avatar
vivran22
Community Champion
6 years ago

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's avatar
    niko18033
    Helper I
    6 years ago

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