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.  ...
  • 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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

No, it is adding all the values. not working.

 

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

Anonymous 

i think its completely what you need.

 

Name Date
A 01.01.2020
A 01.01.2020
A 02.01.2020
A 03.01.2020
A 03.01.2020
A 04.01.2020
B 05.01.2020
B 06.01.2020
B 06.01.2020

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Exactly this is what I need but not working in mine. 

    let me check again otherwise I'll post real data.

     

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

      Anonymous 

      pay attention, I suggested 2 measures, try this statement:

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I am trying this one only.