Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

how to Count distinct date value by Name ?

hello, 

 

How to calculate distinct date value by Name? want dax for it.

I want this column in power bi ( countdistinct of date by Name).

Plus then sum all the values of name single time. 

for example A(5) +B(4)+C(1) = 10 

Not the full sum which is 87.

and there will be same values infront for name so addition of distinct count will not work. 

 

 

thanks 

 

  • az38's avatar
    az38
    6 years ago

    Anonymous 

    as you use a date slicer you could try this

    Measure = 
var _minDate = CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))
var _maxDate = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return
SUMX(SUMMARIZE('Table', 'Table'[Name], "DistinctDates", 
CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]), DATESBETWEEN('Table'[Date], _minDate, _maxDate))),[DistinctDates]
)

    to notificate me about new questions please mark me with @ symbol

14 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    try

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table', 'Table'[Name]) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hello

      this is not working. 

       

      what I want is to add all the value of distinct dates count by the name. 

      example 

      - distinct count of dates by name .

      this is what I want and then sum all the distinctcount of date by name . (Single time) 

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        see my second post. doesn't it ok?

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    sorry, for your second task try

    Measure = SUMX(
SUMMARIZE('Table', 'Table'[Name], "DistinctDates", CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table','Table'[Name]))),
[DistinctDates]
)