DAX Distinct Count

[Anonymous]

I'm trying to do distinct count at a total level by all columns except for one. For example:

My sample data is pasted below:

Quote	Company	Attr1
1	A	N
1	B	N
1	C	N
2	A	N
2	B	N
2	C	N
2	D	N
3	A	Y
3	D	Y
3	E	Y

If the user groups the data by just Company, I would like the pivot table to look like this:

Company	Count
A	3
B	3
C	3
D	3
E	3

I have figured this out by using the following formula = COUNTX(ALL(Data[Quote]),DISTINCTCOUNT([Quote]))

However, if the user segments the view by Attr1 field, then I would like the pivot table to look like this:

Company	N	Y
A	2	1
B	2	1
C	2	1
D	2	1
E	2	1

This is where I am struggling with. Any help would be appreciated. Please note that there are more than just one Attr(n) field in my full dataset.

[amitchandak]

Please try the following. Also is Attr1 part of the same table or different

COUNTX(Data[Quote],DISTINCTCOUNT([Quote]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[Anonymous]

@amitchandak Thanks for the suggestion, but as I mentioned in my question. I have figured out the formula for the first view with just company. I'm struggling with the second view. and Yes, Attr1 is in the same table. Thanks for taking the time.

[amitchandak]

I fail to understand with the given sample data why B is 1 in Y. Can you please provide more info on the second calculation.

[Anonymous · Accepted solution]

@amitchandak I apologize for the mistake. I wanted the output in the second view to look like this.

Company	N	Y
A	2	1
B	2
C	2
D	1	1
E		1

I think I managed to figure it out. =CALCULATE(DISTINCTCOUNT(Data[Quote]),ALL(Data[Quote],Data[Company]))

Do you agree with this?

[amitchandak]

In case it is working out. Then it should be fine.

But All on Quote and company will disable filter on those. I hope that is intentional. And you should get the same result without those too; if there is no other filter.