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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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 @Anonymous 

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 @Anonymous 

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
Anonymous
Not applicable

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 @Anonymous 

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 @Anonymous 

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.