Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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
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
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. |
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |