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

Get 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

Reply
klev28
Helper I
Helper I

Help creating Channel opt in pivot / crosstab table

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

 

klev28_0-1728581579742.png

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!

 

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

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 Email
C002 Email
C002 Mail
C003 Mail
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


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

View solution in original post

v-bofeng-msft
Community Support
Community Support

Hi @klev28 ,

 

I've made a test for your reference:

1\I assume there is a table(Table)

vbofengmsft_0-1728633516643.png

2\Add a caculate table

vbofengmsft_1-1728633546536.png

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

vbofengmsft_2-1728633688029.png

 

Best Regards,

Bof

View solution in original post

4 REPLIES 4
v-bofeng-msft
Community Support
Community Support

Hi @klev28 ,

 

I've made a test for your reference:

1\I assume there is a table(Table)

vbofengmsft_0-1728633516643.png

2\Add a caculate table

vbofengmsft_1-1728633546536.png

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

vbofengmsft_2-1728633688029.png

 

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

 

OwenAuger
Super User
Super User

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 Email
C002 Email
C002 Mail
C003 Mail
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


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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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