Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Distinct Count in DAX

Hi there!

 

I have a question about a certain type of distinct count in DAX.

 

I have a table that lists different customers, their contract number, and the number of locations each customer has. The customer may have different contracts but the total number of locations that is recorded on each contract remains the same. For example, Customer A has three different contracts but the total number of locations the customer has remains the same, in this case 1 location. 

 

I now want to get a total number of locations for this table. The result in the example below should be 5, as customer A has 1 location, Customer B has 3 locations, and Customer C has 1 location. However, if I just sum it up, the result is 18, since it counts everything. Could somebody tell me what measure would be best to get the result I am after? I hope this all makes sense. Thanks a lot!

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Anonymous ,

     

    with DAX you have to take one step more.

    First you should create a virtual table with the customers and the number of locations. Then you can summarize by company.

    The following measure is doing that and should solve your problem:

    Number of Locations V2 = 
-- Create a virtual table of the distinct Customer Values and add the maximum number of locations per Customer
VAR vSummarizedTable =
    ADDCOLUMNS(
        VALUES( myTable[Customer] ),
        "@NumberOfLocations",
            CALCULATE(
                MAX( myTable[Number of Locations] )
            )
    )
RETURN
-- Summarize the number of locations of the table from above
    SUMX(
        vSummarizedTable,
        [@NumberOfLocations]
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

2 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    with DAX you have to take one step more.

    First you should create a virtual table with the customers and the number of locations. Then you can summarize by company.

    The following measure is doing that and should solve your problem:

    Number of Locations V2 = 
-- Create a virtual table of the distinct Customer Values and add the maximum number of locations per Customer
VAR vSummarizedTable =
    ADDCOLUMNS(
        VALUES( myTable[Customer] ),
        "@NumberOfLocations",
            CALCULATE(
                MAX( myTable[Number of Locations] )
            )
    )
RETURN
-- Summarize the number of locations of the table from above
    SUMX(
        vSummarizedTable,
        [@NumberOfLocations]
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      Hey Anonymous ,

       

      did it work with the Measure I provided?

      I'm curious if you could solve this issue 🙂

       

      If my approach helped to make it work, I would be happy if you could mark it as solution ✔️. Like this, the next person who is stumbling across this post can see immediately what worked, without trying all the approaches for themselves.
       
      If you need any help please let me know.
       
      Best regards
      Denis