Help creating Channel opt in pivot / crosstab table

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:

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