Forum Discussion

niko18033's avatar
niko18033
Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper 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
      Icon for Community Champion rankCommunity 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
        Icon for Helper I rankHelper 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")
         
         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        niko18033
        Icon for Helper I rankHelper I
        6 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      [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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.