Forum Discussion
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. ...
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
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
Community Champion
Community ChampionAnonymous
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