Distinct count for every customer row

[Hydramh2]

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?

[daxer-almighty · Accepted solution]

Hi @Hydramh2 

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

[Hydramh2]

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.

[daxer-almighty]

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.

[daxer-almighty]

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.

[Hydramh2]

Hi, granted!

[Anonymous]

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.

[Hydramh2]

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

[Anonymous]

@Hydramh2 

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

[Hydramh2]

@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.

[Anonymous]

Here's your measure:

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

[Hydramh2]

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

[amitchandak]

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

[Hydramh2]

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.

[rfigtree]

the solution is correct.

[Hydramh2]

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?