Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
junglejimsvet
Helper I
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:)

 

CustomerCall IDSeen through which channel
Mr Smith1Virtual
Mr Rogers2Email
Mr Jones3Virtual
Mr Smith4Face to Face
Mr Jones5Face to Face
Mr Rogers6Email
Mr Edwards7Congress
   
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.

 

Thanks in advance for your help:)

 

Jim

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @junglejimsvet 

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
(Similar structure to measure in this article)

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.

OwenAuger_0-1675341456884.png

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

AlB
Super User
Super User

Hi @junglejimsvet 

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

 

SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
junglejimsvet
Helper I
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

AlB
Super User
Super User

Hi @junglejimsvet 

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

 

SU18_powerbi_badge

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.

 

OwenAuger
Super User
Super User

Hi @junglejimsvet 

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
(Similar structure to measure in this article)

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.

OwenAuger_0-1675341456884.png

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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