DAX - Measure to count distinct values when countrows with same ID is 2

[tortugamarina]

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	-

[Ashish_Mathur]

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.

[tortugamarina]

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.

[Ashish_Mathur]

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]

Thanks for your explanation Ashish.

[v-kkf-msft]

Hi @tortugamarina ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[amitchandak]

@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])))

[tortugamarina]

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