Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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:

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?

 

COUNT
DAX
Distinct Counts
Help with DAX
Measures
Visuals
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

    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

15 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Here's your measure:

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, granted!

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.