The count of existing days is not shown at an aggregated level

[sivarajan21]

Hi,

Good Day everyone!

I have below this below sample dataset

Data table

Date	Points	Source
12/05/2022 00:00	PowysCC-1986	Invoice
11/05/2022 00:00	PowysCC-1986	Invoice
10/05/2022 00:00	PowysCC-1986	Invoice
12/05/2022 00:00	PowysCC-1986	Profile
11/05/2022 00:00	PowysCC-1986	Profile
10/05/2022 00:00	PowysCC-1986	Profile
30/04/2022 00:00	PowysCC-1988	Invoice
29/04/2022 00:00	PowysCC-1988	Invoice
28/04/2022 00:00	PowysCC-1988	Invoice
30/04/2022 00:00	PowysCC-1988	Profile
29/04/2022 00:00	PowysCC-1988	Profile
28/04/2022 00:00	PowysCC-1988	Profile

I want  a dax to count the Date column with respect to their points(aggregating to this level) and don't want source column to affect my count. so literally aggregating date count to points level. I need this dax to show full count of dates for their respective points. 

I created this below measure to solve this but I am getting below problem:

Count of Exisitng Days =
DISTINCTCOUNT ( data[Date] )

The problem i have now is shown in screenshot below:

When I don't filter by points it shows only 23 dates which is not correct. There are 46 rows in this data table.  When I filter by PowysCC-1986 it shows 12 days which is correct.

Please find the sample file attached here.

Can you please me with this?

Thanks in advance.

@Ahmedx @grantsamborn 

[sivarajan21]

Hi @Ahmedx ,

Thanks for your quick response!

Kudos to you for this solution and it works brilliantly. This measure returns all rows and thanks for this.

However, In my below sample dataset

Date	Points	Source
12/05/2022 00:00	PowysCC-1986	Invoice
11/05/2022 00:00	PowysCC-1986	Invoice
10/05/2022 00:00	PowysCC-1986	Invoice
12/05/2022 00:00	PowysCC-1986	Profile
11/05/2022 00:00	PowysCC-1986	Profile
10/05/2022 00:00	PowysCC-1986	Profile
30/04/2022 00:00	PowysCC-1988	Invoice
29/04/2022 00:00	PowysCC-1988	Invoice
28/04/2022 00:00	PowysCC-1988	Invoice
30/04/2022 00:00	PowysCC-1988	Profile
29/04/2022 00:00	PowysCC-1988	Profile
28/04/2022 00:00	PowysCC-1988	Profile

for point PowysCC-1986, total date count is 6, out of which only 3 is unique. Because the point id only one but dates are duplicate. so 3 is the distinct one. Although this measure (Count of Exisitng Days =
DISTINCTCOUNT ( data[Date] )) works, correctly at low level(sample data) but when I apply this to my large dataset which contains almost 1 million records, it doesn't work correctly, but where as, this measure works,

Count of Exisitng Days tan =
VAR dates =
SUMMARIZE ( data1, data1[Points], data1[Date].[Date] )
VAR Ext_dates =
COUNTROWS ( dates )
RETURN
Ext_dates

The result comparison of both measures are as shown in below screenshot:

So now my question would be, is there any alternative measure that can replace measure

'Count of Exisitng Days tan' in terms of efficiency?

It would be much appreciated if you can help me on out on this.

Once this is done, I can close this query.

Thanks in advance.

[Ahmedx]

I'm not sure if understood you, but the following measure works faster

Count of Exisitng Days tan = 
SUMX(DISTINCT('data'[Date]),1)

[sivarajan21]

Hi @Ahmedx ,

Thanks for your quick response!

I tried this solution and we are pretty closer, and this works faster.

Solution varies slightly as shown below:

I am sorry i was not able to give a clear picture. Anyway i will accept your previous solution and close this query.

Thanks a lot sir

[Ahmedx · Accepted solution]

pls try this

Count of Exisitng Days = 
    if ( 
        ISFILTERED('points'[Points]),
            DISTINCTCOUNT ( data[Date] ),COUNTROWS('data'))