DAX command to find count of distinct customer for multiple product combinations

[nan_lp]

Greetings!

I have a table like this,

User Name	Mobile
123	iPhone
123	Moto
123	Moto
123	OnePlus
222	iPhone
333	Moto
333	OnePlus
444	Moto
444	OnePlus

I need to count distinct user across different mobile combinations like below

Mobile Combinations	Distinct Count of User Name
iPhone + Moto + OnePlus	1
Moto + OnePlus	2
iPhone	2
Moto	3
OnePlus	3

Could you please help with the DAX query

@amitchandak 

 

[jgeddes · Accepted solution]

Here is a DAX code you can use to create a calculated table if you want to go that way.

Calculated Table = 
var _comboTable =
SUMMARIZE(
    SUMMARIZE('Table', 'Table'[User Name], "__value", CONCATENATEX(DISTINCT('Table'), [Mobile], " + ")),
    [__value],
    "__user", DISTINCTCOUNT('Table'[User Name]),
    "_isCombo", FIND("+", [__value], 1, -1)
)
var _individualTable =
SUMMARIZE(
    'Table',
    'Table'[Mobile],
    "__user", DISTINCTCOUNT('Table'[User Name])
)
var _resultTable = 
UNION(
    SELECTCOLUMNS(FILTER(_comboTable, [_isCombo] >= 0), "Mobile Combinations", [__value], "Distinct User Count", [__user]),
    SELECTCOLUMNS(_individualTable, "Mobile Combinations", 'Table'[Mobile], "Distinct User Count", [__user])
)  
RETURN
_resultTable

'Table' would need to be replaced with your table name. 
The resulting table would like the following with the sample data you provided.

[v-agajavelly]

Hi @nan_lp ,

Just checking in one last time haven’t seen you back in a while. As per the Microsoft Fabric Community Forum guidelines, we’ll go ahead and close this thread for now. If your issue has already been resolved, that’s great to hear.

But if you still need help down or anything else feel free to create a new post anytime. The community is always here to support you.

Regards,
Akhil.

[v-agajavelly]

Hi @nan_lp ,

Just checking in were you able to take a look at the suggestions or the PBIX file I shared? Would love to hear how it worked out for you. And if you need a hand applying it to your own model, feel free to reach out happy to help.

Regards,
Akhil.

[v-agajavelly]

Hi @nan_lp ,

Just checking in to see if you had a chance to try out the suggestions or take a look at the PBIX file shared?

Would happy to hear how it worked out for you  and feel free to shout if you need any help implementing it in your own model.

Regards,
Akhil.

[v-agajavelly]

Hi @nan_lp ,

Thanks for posting your interesting DAX scenario.
Just following up, the solutions shared by @ExcelMonke , @jgeddes  and @Ashish_Mathur  on above are correct and effectively address your requirement to calculate distinct user counts across different mobile combinations. The approach using calculated tables or measures along with a PBIX file provided gives a clear demonstration of how to break down and analyze distinct user participation across multiple product sets.

If you've had a chance to review and test the file or implement the logic, we’d love to hear your feedback or if you need any further clarification.

Thanks again to all the contributors for the helpful responses.

Regards,
Akhil

[Ashish_Mathur · Accepted solution]

Hi,

This is what we typically call a market basket study i.e. "Of all those who own an iphone how many also own a Motorola".  I have solved a similar question in the attached files.  Please study these and adapt the solution there to suit your needs.

Hope this helps.

[jgeddes · Accepted solution]

Here is a DAX code you can use to create a calculated table if you want to go that way.

Calculated Table = 
var _comboTable =
SUMMARIZE(
    SUMMARIZE('Table', 'Table'[User Name], "__value", CONCATENATEX(DISTINCT('Table'), [Mobile], " + ")),
    [__value],
    "__user", DISTINCTCOUNT('Table'[User Name]),
    "_isCombo", FIND("+", [__value], 1, -1)
)
var _individualTable =
SUMMARIZE(
    'Table',
    'Table'[Mobile],
    "__user", DISTINCTCOUNT('Table'[User Name])
)
var _resultTable = 
UNION(
    SELECTCOLUMNS(FILTER(_comboTable, [_isCombo] >= 0), "Mobile Combinations", [__value], "Distinct User Count", [__user]),
    SELECTCOLUMNS(_individualTable, "Mobile Combinations", 'Table'[Mobile], "Distinct User Count", [__user])
)  
RETURN
_resultTable

'Table' would need to be replaced with your table name. 
The resulting table would like the following with the sample data you provided.

[ExcelMonke]

Hello,
You can consider the following first step to help get your DAX. Just remember that when pulling this into a matrix, each measure will be its own column; to get it formatted in the table you have posted, you'll need to consider something like a SWITCH function to get the formatting correct.

Your base DAX will look like something this:

Measure = 
CALCULATE(DISTINCTCOUNT(Table[User Name]), Table[Mobile] = "iPhone" && Table[Mobile] = "Moto" && Table[Mobile] = "OnePlus")