Forum Discussion
DAX - Measure to count distinct values when countrows with same ID is 2
Hi all,
I am trying to calculate:
1) Number of Departments where each customer has bought
2) For those customers who bought in 2 different departments, what is the total count of those departments
3) not sure if possible, create a matrix to represent the count combination
Here is the sample of the dataset:
| customerID | Department | Amount | Date |
| 1 | Salad | 20 | 10-jan-21 |
| 1 | Burger | 9 | 10-jan-21 |
| 1 | Drinks | 13 | 10-jan-21 |
| 2 | Salad | 18 | 10-jan-21 |
| 2 | Drinks | 20 | 10-jan-21 |
| 3 | Coffee | 9 | 10-jan-21 |
| 4 | Salad | 5 | 10-jan-21 |
| 5 | Salad | 9 | 10-jan-21 |
| 5 | Burger | 6 | 10-jan-21 |
| 6 | Salad | 15 | 10-jan-21 |
| 6 | Burger | 20 | 10-jan-21 |
| 7 | Drinks | 12 | 10-jan-21 |
| 7 | Coffee | 10 | 10-jan-21 |
With this calculation I got the following table
Count Distinct Dep =
sumx(SUMMARIZE(Articles,Articles[Departments],"Count dep",count(cust[customerID])),[Count dep])
| customerID | Count Deparments |
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
Now I would like to select only with Count department = 2
| customerID | Distinct Deparments = 2 | Department | Department |
| 2 | x | Salad | Drinks |
| 5 | x | Salad | Burger |
| 6 | x | Salad | Burger |
| 7 | x | Drinks | Coffee |
And count the distinct values of the Departments for those clients, but these different calculation are not working...
Two Dep Count Distinct =
COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) ))
This following one returns the correct number of clients buying in 2 departments, but when visualizing with Department list, it crashes.
Dep count 2 grouping =
COUNTROWS (
FILTER (
VALUES ( cust[customerID] ),
CALCULATE ( [Count Distinct Dep]=2)
))
This should be the desired output:
| Count | |
| Salad | 3 |
| Burger | 2 |
| Drinks | 2 |
| Coffee | 1 |
It is being quite tricky to find information about how to create this matrix, where you can visualize what are the pairs combination. In other terms, the previous result buy providing more information about how the Departments relate eachother.
| Salad | Burger | Drinks | Coffee | |
| Salad | - | 2 | 1 | |
| Burger | 2 | - | ||
| Drinks | 1 | - | 1 | |
| Coffee | 1 | - |
7 Replies
Hi,
This measure works
Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[customerID]),Data[customerID],"ABCD",COUNTROWS(Data),"EFGH",CALCULATE(COUNTROWS(Data),ALL(Data[Department]))),[ABCD]>0&&[EFGH]=2))Hope this helps.
Hi,
This solution is not working for me. I get totally random values that does not make sense. Could you please explain a bit the DAX and name ABCD and EFGH in a way that I can understand what they calculate?Thank you.
I do not what the reason could be. You can very clearly see in my screenshot that it works fine on the data that you had shared.
tortugamarina , While showing at department level you have to igonre department filter .
example
Two Dep Count Distinct =
calculate(COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) )), filter(allselected(cust), Table[cust] = max(Table[cust])))Hi,
I don´t get the result desired. In fact, MAX function is not possible to use with a Table, but needs a specific column, and if I replace the cust for cust[customerID] it results in blank:Two Dep Count Distinct = calculate(COUNTROWS (FILTER (VALUES (cust[customerID]), CALCULATE([Count Distinct Dep]=2) )), filter(allselected(cust), cust[customerID] = max(cust[customerID])))
