Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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 | |
Mr Jones | 3 | Virtual |
Mr Smith | 4 | Face to Face |
Mr Jones | 5 | Face to Face |
Mr Rogers | 6 | |
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.
Thanks in advance for your help:)
Jim
Solved! Go to Solution.
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.
Regards,
Owen
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_ )
|
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. |
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
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_ )
|
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. |
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.
Regards,
Owen
User | Count |
---|---|
90 | |
88 | |
85 | |
80 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |