how to Count distinct date value by Name ?

[Anonymous]

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]

@Anonymous 

sorry, for your second task try

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

[az38]

Hi @Anonymous 

try

Measure = CALCULATE(DISTINCTCOUNT('Table'[Date]), ALLEXCEPT('Table', 'Table'[Name]) )

[Anonymous]

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]

@Anonymous 

see my second post. doesn't it ok?

[Anonymous]

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

[az38]

@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]

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

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

[az38]

@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]

I am trying this one only. 

[az38]

@Anonymous 

maybe you have more complicated data model then in example?

[Anonymous]

INRW01954	15-01-2020
INNS02503	15-01-2020
INSG01563	15-01-2020
INJA01639	01-01-2020
INSS00267	01-01-2020
INKM01114	01-01-2020
INAR01371	01-01-2020
INMS00148	01-01-2020
INNK01675	01-01-2020
INVR00406	01-01-2020
INYD00784	01-01-2020
INFK00954	01-01-2020
INJS00801	01-01-2020
INGM01331	01-01-2020
INAB01141	01-01-2020
INAI01710	01-01-2020
INAJ01027	01-01-2020
INMK02479	01-01-2020
INBR00802	01-01-2020
INDK00496	01-01-2020
INBT01170	01-01-2020
INGS00982	01-01-2020
INSS01931	01-01-2020
INAK00879	01-01-2020
INSK01511	01-01-2020
INAH01897	01-01-2020
INSS01592	01-01-2020
INSS00826	01-01-2020
INAS02633	01-01-2020
INVC00436	01-01-2020
INNA00674	01-01-2020
INRP00689	01-01-2020
INVP00804	01-01-2020
INRB02400	15-01-2020
INDP00831	01-01-2020
INPS01950	01-01-2020
INCB01808	15-01-2020
INVP00804	15-01-2020
INDU01645	01-01-2020
INTS01338	01-01-2020
INSS01107	01-01-2020
INPN02033	01-01-2020
INMK01836	01-01-2020
INAK02547	01-01-2020
INAK00503	01-01-2020
INAR00520	01-01-2020
INAB00693	01-01-2020
INAD01916	01-01-2020
INSN01775	01-01-2020
INSY02563	01-01-2020
INAP02048	15-01-2020
INDB02548	15-01-2020
INAS01974	01-01-2020
INDK01582	01-01-2020
INNV02390	01-01-2020
INKP01798	01-01-2020
INMP01425	15-01-2020
INKP01920	15-01-2020
INKK02020	15-01-2020
INUC02578	01-01-2020
INAY01570	01-01-2020
INNV02390	15-01-2020
INME02543	15-01-2020
INRS01496	15-01-2020
INMK01628	15-01-2020
INSN01849	15-01-2020
INAY01570	15-01-2020
INSB01739	01-01-2020
INSS01809	15-01-2020
INAG01856	15-01-2020
INPG01484	15-01-2020
INNS01796	15-01-2020
INNG01782	15-01-2020
INAK01829	15-01-2020
INSK02600	15-01-2020
INSY02563	15-01-2020
INMS00510	15-01-2020
INRP01681	15-01-2020
INSM01842	15-01-2020
INVK01813	15-01-2020
INRG01302	01-01-2020
INRG02500	15-01-2020
INRG01302	15-01-2020
INUC02578	15-01-2020
INKB02535	15-01-2020
INVN02413	15-01-2020
INSK01988	15-01-2020
INCM01749	15-01-2020
INKK02387	15-01-2020
INSC02403	15-01-2020
INKK02387	01-01-2020
INAS01974	15-01-2020
INPK01497	15-01-2020
INKP01798	15-01-2020
INDK01582	15-01-2020
INRK02539	01-01-2020
INSY02540	15-01-2020
INDK02469	15-01-2020
INRK02539	15-01-2020
INSB01739	15-01-2020
INKG02056	15-01-2020
INBM01342	15-01-2020
INNG01782	01-01-2020
INKD02498	01-01-2020
INDB02548	01-01-2020
INKD02498	15-01-2020
INAM01812	15-01-2020
INVK01813	01-01-2020

This is the data I am using, and just for your Infromation there were a lot of more columns which I have detailed because of confidential data.

[az38]

@Anonymous 

it works the same even with additional columns

Could you display where is result incorrect?

[Anonymous]

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 · Accepted solution]

@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