Forum Discussion
Overlap table
Than you for your help! However this is just a subset. Doing this for the whole dataset would take quite a lot of time, because there are many combinations possible.
In another topic I found this:
This did not bring me to the exact table I wanted, so I did the rest by hand. However this took quite a lot of hand work, because all all possible orderings of products appeared in different cells (a,b,c - a,c,b - b,a,c - etc.).
Community SupportAnonymous ,
So your requirement is to achieve the distinct count of zipcode with three combinations "A&B", "B&C" and "A&C", right? You can create three calculate columns and three measures using DAX below:
Calculate columns:
Combination A&B = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "A" || 'Table'[Product] = "B"))
Combination B&C = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "B" || 'Table'[Product] = "C"))
Combination A&C = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "A" || 'Table'[Product] = "C"))
Measures:
Count A&B = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination A&B] = "A&B"))
Count B&C = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination B&C] = "B&C"))
Count A&C = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination A&C] = "A&C"))
The result will be like below:
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Than you for your help! However this is just a subset. Doing this for the whole dataset would take quite a lot of time, because there are many combinations possible.
In another topic I found this:
This did not bring me to the exact table I wanted, so I did the rest by hand. However this took quite a lot of hand work, because all all possible orderings of products appeared in different cells (a,b,c - a,c,b - b,a,c - etc.).