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...
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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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.

 

  • rfigtree's avatar
    rfigtree
    Icon for Resolver III rankResolver III
    5 years ago

    the solution is correct.

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