topic Re: Distinct count for every customer row in DAX Commands and Tips

Distinct count for every customer row

Anonymous — Wed, 17 Mar 2021 21:13:38 GMT

Hi, I have two tables: DimSubjects (dimension) where I have all the customer and a FactSales (fact table) where I have sales transaction. They are related by a subject key (one to many) and I display the data in a table visual (see image).

Nome, is the customer name and Documento di trasporto is the sales id:

I want to create a new field in the visual in which I calculate the sum of all the distinct sales id for each client.

The result should be this:

I tried to do the DISTINCTCOUNT(SalesId) but it creates a column of all 1 values.

How can I sum it and group it by customer and display it on every row that customer is displayed?

Re: Distinct count for every customer row

amitchandak — Thu, 18 Mar 2021 04:10:51 GMT

Anonymous , try a new column

calculate(DISTINCTCOUNT(table[documentdo di transorto]) , filter(all(table), table[Nome] = earlier(table[Nome])))

or a new measure

calculate(DISTINCTCOUNT(table[documentdo di transorto]) , filter(all(table), table[Nome] = max(table[Nome])))

Re: Distinct count for every customer row

Anonymous — Thu, 18 Mar 2021 08:04:14 GMT

Hi, I've tried both your solutions but it still give me the value 1 in each row and that's not what I want.

Re: Distinct count for every customer row

rfigtree — Thu, 18 Mar 2021 10:11:29 GMT

the solution is correct.

Re: Distinct count for every customer row

Anonymous — Thu, 18 Mar 2021 10:31:39 GMT

I used this measure:

n° ddt = CALCULATE(DISTINCTCOUNT('F - Sales packing slip'[documentdo di transPorto]), FILTER(ALL('D - Subject'), 'D - Subject'[Nome] = MAX('D - Subject'[Nome])))

and here it's the result:

Maybe I've made some mistakes in the formula?

Re: Distinct count for every customer row

Anonymous — Thu, 18 Mar 2021 14:12:58 GMT

Here's your measure:

CALCULATE( COUNTROWS( FactSales ), REMOVEFILTERS( FactSales[Documentdo di transporto] ) )

Re: Distinct count for every customer row

Anonymous — Fri, 19 Mar 2021 08:00:38 GMT

Hi Anonymous , thanks for your reply.

Unfortunately your measure causes this to happen (the result rows are multiplied endlessly and the count is not correct, it should be 6):

In the SalesTable there are multiple lines for each Documento di trasporto. For example this Documento di trasporto has two lines:

so that's why for that Customer we have a count of 9 in total (it counts some documento di trasporto 2 times because they have two lines):

Re: Distinct count for every customer row

Anonymous — Fri, 19 Mar 2021 08:49:56 GMT

Anonymous

Well, I gave you an idea (I don't know the layout of your model and if I don't, then I can't guarantee 100% correctness of my solution, obviously)... You should have built on this. But I'll do it for you. Instead of COUNTROWS( FactTable ) use DISTINCTCOUNT( FactTable[Documento di transporto] ).

Re: Distinct count for every customer row

Anonymous — Fri, 19 Mar 2021 09:10:22 GMT

Anonymous

Yes, thanks, I appreciate your help. Unfortunately I've already tried and the result is the same as the measure we tried before. I cannot do removefilters on a field while I'm doing a distinctcount on that field and displaying it on the visual:

I imagine there's no solution for that. That's not a trivial problem that can be resolve with a simple formula.

Re: Distinct count for every customer row

Anonymous — Fri, 19 Mar 2021 09:15:56 GMT

It's an easy problem but you are not giving us the full picture. Hence the difficulty. How one should write DAX depends heavily on the underlying model. We can't see the model, so... DAX is only a mere guess.

Re: Distinct count for every customer row

Anonymous — Wed, 31 Mar 2021 09:14:42 GMT

Hi Anonymous, you're right. I was able to upload the file with the data in cloud, you can find it here: https://drive.google.com/file/d/1c6k_mCotPjFSzj1vAgrRT97w2UqRwRW4/view?usp=sharing

What I would like to do is to distinct count the field "Documento di trasporto" for each "Nome" in the time period selected on the upper time filter.

What I expect is the value 4 for COMEK SRL, 1 for F.R. ENGINEERING SRL and 14 for SACMI VERONA SPA

Re: Distinct count for every customer row

daxer-almighty — Wed, 31 Mar 2021 11:13:45 GMT

Hi there.

It's daxer but from a home PC, so I have a different user name. Please grant access to the file as I currently can't download it. Thx.

Re: Distinct count for every customer row

Anonymous — Wed, 31 Mar 2021 11:17:03 GMT

Hi, granted!

Re: Distinct count for every customer row

daxer-almighty — Wed, 31 Mar 2021 12:09:55 GMT

Mate, you've changed names of the fields in the visual. This does not help 😞 I can't even find the fields in the tables and I don't have time to hunt for them. You shouldn't have changed the names as it makes troubleshooting a lot harder.

Re: Distinct count for every customer row

daxer-almighty — Wed, 31 Mar 2021 12:34:57 GMT

Hi Anonymous

I've created the measure but please remember next time not to change the names of the fields in the visual as this makes it really much harder to work with the model for a person that has not worked with the model. Nobody wants to spend time on deciphering step by step, painstakingly, which field belongs to which table.

Here's the measure but I can't guarantee it'll be fast:

The Count = var vCurrentSubjectID = DISTINCT( 'D - Subject'[Subject ID] ) var vResult = if( NOT ISEMPTY( 'F - Sales packing slip' ), CALCULATE( DISTINCTCOUNT( 'F - Sales packing slip'[Packing slip ID] ), 'D - Subject'[Subject ID] in vCurrentSubjectID, ALLSELECTED( 'F - Sales packing slip' ) ) ) return vResult

Re: Distinct count for every customer row

Anonymous — Thu, 01 Apr 2021 07:22:36 GMT

Hi daxer-almighty , now it works thanks to your help. I'm very sorry to have make you lose time for the names that I've changed in the report. It was the first time for me to share a file here, but now I've understand how to do that and, most of all how to implement that formula. Thank you and have a wonderful day.