Column to filter out only single-client values

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

[mhossain]

Hi @niko18033 

There are ovbiously multiple way to solve this, try below steps.

--Go to query editor window and use 'Group BY' functionality to create a table where you have 'Email' and in second column 'Distinct count of client, and name this table 'Email_mapping', basically in this table you have the Emails where you can identify which emails are associated to 1 or 1+ clients.

--Filter out the 1 (or filter >=2) in count column and close and apply.

-- Now create the relationship between this newly created 'Email_mapping' and your main table in your Model view.

--Drag the Emails from 'Email_mapping' table to your chart and it should be fine now.

Hope above points make sense

--On the chart

[niko18033]

Thanks for your quick reply. This seems feasible, but what if I only want to give the user an option to filter to emails associated with single or multiple clients? That's why I was thinking of creating a column in DAX, rather than the query editor. If I close and apply the changes, that toggle won't be visible to the end-user, correct?

[jthomson]

Rather than explicitly filtering it out in Power Query, you could retain that as a column (you might want to consider making an additional column to say if [oldcolumn]=1 then "single" else "multiple") and drop that into a filter of its own?