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

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

Anonymous 

it works the same even with additional columns

Could you display where is result incorrect?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This is power bi result I am getting. 

Measure = SUMX(
SUMMARIZE(Raw_BusinessWorld,Raw_BusinessWorld[Resource_Id], "DistinctDates", CALCULATE(DISTINCTCOUNT(Raw_BusinessWorld[Trans_Date]), ALLEXCEPT(Raw_BusinessWorld,Raw_BusinessWorld[Resource_Id]))),
[DistinctDates]
)
using this dax
 
and this is the excel exported result of the column 

result should be like yellow column and the answer should be 22 

but it is giving 335. 

thanks

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    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