cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hydramh2
Frequent Visitor

Distinct count for every customer row

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:

Hydramh2_1-1616015118106.png

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:

Hydramh2_2-1616015350710.png

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?

 

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

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

View solution in original post

15 REPLIES 15
daxer-almighty
Solution Sage
Solution Sage

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

View solution in original post

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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.

Hi, granted!

daxer
Solution Sage
Solution Sage

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
Frequent Visitor

Hi @daxer, 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

daxer
Solution Sage
Solution Sage

@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
Frequent Visitor

@daxer 

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:

Hydramh2_0-1616144688235.png

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

daxer
Solution Sage
Solution Sage

Here's your measure:

 

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

 

Hydramh2
Frequent Visitor

Hi @daxer , 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):

Hydramh2_0-1616140170772.png

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

Hydramh2_1-1616140539786.png

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

Hydramh2_2-1616140620546.png

 

 

amitchandak
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

 

the solution is correct.

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:
ind.PNG
Maybe I've made some mistakes in the formula?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates