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 moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi
I'm not sure how to word my question but this is the kind of table that I want to produce (without the totals is fine, just added them in to illustrate the data):
Where the numbers are customer counts. So the above mock up is 1,000 customers of which 800 are email opt in, 600 are mail opt in and 250 are phone opt in. The chart shows this plus the crossover between any 2 channels.
I'm not sure how to model the data to support this visual / what vis to select / what measure to create!
My source data is currently in the below summary format but of course I am happy to remodel if needed:
Customer ID, is Email, is Mail, is Phone
C001,1,0,0
C002,1,1,0
C003,0,1,1
C004,0,0,1
etc
I'm also open to rethinking how to show this crossover information. Like if I can set up something to let my users select which channels they want to include and show all combinations and maybe add in a heirarchy order that would be amazing. But I thought I'd first try a simple cross tab and got stuck 😛
Thanks for any pointers. I'm currently rudderless with this one!
Solved! Go to Solution.
Hi @klev28
I would recommend the "basket analysis" pattern for this.
See here for an explanation:
https://www.daxpatterns.com/basket-analysis/
For this pattern to work, you should unpivot the columns "is Email", "is Mail", "is Phone" so that you end up with this structure:
Customer | Channel |
C001 | |
C002 | |
C002 | |
C003 | |
C003 | Phone |
C004 | Phone |
... | ... |
Then, if you follow the article, you should create two copies of a Channel dimension.
The # Orders Both measure from the article is similar to the one you're displaying in your example. To produce a matrix like your example, you would place one copy of Channel on rows and the other copy on columns.
Please post back if you need more help on the details.
Regards
Hi @klev28 ,
I've made a test for your reference:
1\I assume there is a table(Table)
2\Add a caculate table
Channels =
DATATABLE (
"Channel", STRING,
{
{ "Email"},
{ "Mail"},
{ "Phone"},
{ "Total" }
}
)
3\Add 4 measures for the caculate table 'Channels'
Email =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1 )),
"Phone",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Email]=1))
)
Mail =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 )),
"Phone",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Mail]=1))
)
Phone =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1)),
"Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Phone]=1))
)
Total =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 )),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1)),
"Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )),
"Total",COUNTROWS('Table')
)
4\Add a table visual
Best Regards,
Bof
Hi @klev28 ,
I've made a test for your reference:
1\I assume there is a table(Table)
2\Add a caculate table
Channels =
DATATABLE (
"Channel", STRING,
{
{ "Email"},
{ "Mail"},
{ "Phone"},
{ "Total" }
}
)
3\Add 4 measures for the caculate table 'Channels'
Email =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1 )),
"Phone",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Email]=1))
)
Mail =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 )),
"Phone",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Mail]=1))
)
Phone =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1)),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1)),
"Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )),
"Total",COUNTROWS(FILTER('Table','Table'[is Phone]=1))
)
Total =
Switch(
SELECTEDVALUE(Channels[Channel]),
"Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 )),
"Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1)),
"Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )),
"Total",COUNTROWS('Table')
)
4\Add a table visual
Best Regards,
Bof
Hi Bof
This is a really nice neat solution and I like that it will scale fairly easily as I actually have a 4th channel to add in (SMS). Thank you so much for taking the time to model it out for me!
I've sucessfully replicated this and it is both perfect and easy to understand 🙂
Thanks so much, Kezl
Hi @klev28
I would recommend the "basket analysis" pattern for this.
See here for an explanation:
https://www.daxpatterns.com/basket-analysis/
For this pattern to work, you should unpivot the columns "is Email", "is Mail", "is Phone" so that you end up with this structure:
Customer | Channel |
C001 | |
C002 | |
C002 | |
C003 | |
C003 | Phone |
C004 | Phone |
... | ... |
Then, if you follow the article, you should create two copies of a Channel dimension.
The # Orders Both measure from the article is similar to the one you're displaying in your example. To produce a matrix like your example, you would place one copy of Channel on rows and the other copy on columns.
Please post back if you need more help on the details.
Regards
Hi Owen
Thanks for this. I've used the other solution for my Channel crosstab but the basket analysis that you highlighted is also very relevant and I will almost certainly be using this for the product page. It will save having to create a page per product group - which was also on my list of solutions to find.
I'm seriously delighted with both solutions 😄
Kezl
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |