Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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!

Anonymous
Not applicable

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.

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
Not applicable

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

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

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.

Anonymous
Not applicable

Here's your measure:

 

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

 

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

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
Super User

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors