cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Super quick one:) Count of customers who have been seen by more than one channel

Hi super people,

I'm sure its simple but can't get my head around it:).  I simply want a measure to count how many customers have been seen by more than one channel (so from the example below, 2... Smith and Jones).  Quick pointer would be very appreciated:)

 Customer Call ID Seen through which channel Mr Smith 1 Virtual Mr Rogers 2 Email Mr Jones 3 Virtual Mr Smith 4 Face to Face Mr Jones 5 Face to Face Mr Rogers 6 Email Mr Edwards 7 Congress Need Count of Customers seen though more than one channel

The overall goal is to have % with numerator of customers seen by more than one channel and denominator of customers seen.

Jim

2 ACCEPTED SOLUTIONS
Super User

To calculate this, you have to take a table of distinct customers, filter that table to those with more than 1 channel, and count the rows of the resulting table.

There are multiple ways to do this, and here are a couple. V1 should perform better in general, but V2 is shorter code.

V1

``````Customers Seen By More Than One Channel V1 =
VAR CustomerChannel =
SUMMARIZE (
Calls,
Calls[Customer],
Calls[Channel]
)
VAR CustomerNumChannel =
GROUPBY (
CustomerChannel,
'Calls'[Customer],
"@NumChannel",
SUMX ( CURRENTGROUP (), 1 )
)
VAR CustomerMoreThanOneChannel =
FILTER (
CustomerNumChannel,
[@NumChannel] > 1
)
RETURN
COUNTROWS ( CustomerMoreThanOneChannel )``````
``````Customers Seen By More Than One Channel V2 =
COUNTROWS (
FILTER (
VALUES ( Calls[Customer] ),
CALCULATE ( DISTINCTCOUNT ( Calls[Channel] ) ) > 1
)
)``````

The measure would need to be changed if you intend to use it in a visual including Channel, and you want the measure to look outside the currently filtered Channel.

Small example PBIX attached.

Regards,

Owen

Owen Auger
Blog
Super User

Try this measure:

``````Measure =
VAR custSeen_ = DISTINCTCOUNT ( Table1[Customer] )
VAR custSeenMore1Chan_ =
COUNTROWS (
FILTER (
DISTINCT ( Table1[Customer] ),
CALCULATE ( DISTINCTCOUNT ( Table1[Seen through which channel] ) ) > 1
)
)
RETURN
DIVIDE ( custSeenMore1Chan_, custSeen_ )``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

3 REPLIES 3
Helper I

Hi both, thanks so much for this - perfect.  As ever I am always in awe of your guys skills and prearedness to help others

Many thanks

Jim

Super User

Try this measure:

``````Measure =
VAR custSeen_ = DISTINCTCOUNT ( Table1[Customer] )
VAR custSeenMore1Chan_ =
COUNTROWS (
FILTER (
DISTINCT ( Table1[Customer] ),
CALCULATE ( DISTINCTCOUNT ( Table1[Seen through which channel] ) ) > 1
)
)
RETURN
DIVIDE ( custSeenMore1Chan_, custSeen_ )``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Super User

To calculate this, you have to take a table of distinct customers, filter that table to those with more than 1 channel, and count the rows of the resulting table.

There are multiple ways to do this, and here are a couple. V1 should perform better in general, but V2 is shorter code.

V1

``````Customers Seen By More Than One Channel V1 =
VAR CustomerChannel =
SUMMARIZE (
Calls,
Calls[Customer],
Calls[Channel]
)
VAR CustomerNumChannel =
GROUPBY (
CustomerChannel,
'Calls'[Customer],
"@NumChannel",
SUMX ( CURRENTGROUP (), 1 )
)
VAR CustomerMoreThanOneChannel =
FILTER (
CustomerNumChannel,
[@NumChannel] > 1
)
RETURN
COUNTROWS ( CustomerMoreThanOneChannel )``````
``````Customers Seen By More Than One Channel V2 =
COUNTROWS (
FILTER (
VALUES ( Calls[Customer] ),
CALCULATE ( DISTINCTCOUNT ( Calls[Channel] ) ) > 1
)
)``````

The measure would need to be changed if you intend to use it in a visual including Channel, and you want the measure to look outside the currently filtered Channel.

Small example PBIX attached.

Regards,

Owen

Owen Auger
Blog

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors