Distinctcount with crossfilter issue

[Anonymous]

Good afternoon,

I have an issue that I can solve.

I have this code : 

CALCULATE(COUNTX(FILTER(Base_PBI,Base_PBI[Date1]<=max('new measure'[Date]) && ((Base_PBI[Date2]>DATE(YEAR(TODAY()),month(today()-1),31)) || Base_PBI[Date2]>max('new measure'[Date]))),Base_PBI[Ent]),CROSSFILTER(Base_PBI[Date1],'new measure'[Date],None))

and now I'm trying to count only the distinct value. I tried with this code :

CALCULATE(DistinctCOUNT(Base_PBI[Ent]),FILTER(Base_PBI,Base_PBI[Date1]<=max('new measure'[Date]) && ((Base_PBI[Date2]>DATE(YEAR(TODAY()),month(today()-1),31)) || Base_PBI[Date2]>max('new measure'[Date]))),CROSSFILTER(Base_PBI[Date1],'new measure'[Date],None))

but it doesnt work. Do you know what's wrong with it?

Thank you

[Anonymous]

Hi @Anonymous ,

Sorry for that the information you have provided is not making the problem clear to me.

Can you please share more details to help us clarify your scenario?Or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[Anonymous]

Hello @Anonymous ,

I tried to share with you my pbix file but it doesn't work. So I'm joining a table with my dates.

In power BI, I created a new table that provides me all the date between 01/01/2019 and 31/12/2021. And I would like thanks to this table to have the number of ID that have a start date before the date of this new table and a end_date after it. It will be the number of ID available at this date.

But as I have some similar ID, Hence I only want the count for unique value. That's why I used distinctcount.

There is an active relation between my table with dates between 2019 and 2021 and the table below by the start_date.

I tried this code to have the amount of unique ID available to a given date but it doesnt work.

CALCULATE(DistinctCOUNT(Base_PBI[Ent]),FILTER(Base_PBI,Base_PBI[Start_Date]<=max('new measure'[Date]) && ((Base_PBI[End_Date]>DATE(YEAR(TODAY()),month(today()-1),31)) || Base_PBI[End_Date]>max('new measure'[Date]))),CROSSFILTER(Base_PBI[Start_Date],'new measure'[Date],None))

table with my ID, start_Date and End_Date

ID	Start_Date	End_Date
155	01/01/2020	15/10/2020
155	01/02/2020	01/01/2058
155	01/05/2021	01/01/2079
123	30/01/2021	30/08/2021
155	01/01/2021	01/10/2021
2548	01/01/1988	30/01/2029
89	01/01/2021	30/12/2021
966	12/05/2018	12/05/2019
89	01/01/2020	15/10/2020
89	01/02/2020	01/01/2058
66	01/05/2021	01/01/2079
66	30/01/2021	30/08/2021
32	01/01/2021	01/10/2021
32	01/01/1988	30/01/2029
66	01/01/2021	30/12/2021
2548	12/05/2018	12/05/2019
78	01/01/2020	15/10/2020
5458	01/02/2020	01/01/2058
211	01/05/2021	01/01/2079
211	30/01/2021	30/08/2021
3669	01/01/2021	01/10/2021
587	01/01/1988	30/01/2029
587	01/01/2021	30/12/2021
587	12/05/2018	12/05/2019
456	01/01/2020	15/10/2020
3215	01/02/2020	01/01/2058
85211	01/05/2021	01/01/2079
52145	30/01/2021	30/08/2021
698	01/01/2021	01/10/2021
3221	01/01/1988	30/01/2029
3221	01/01/2021	30/12/2021
3221	12/05/2018	12/05/2019

I would like to have a report like this (the number of unique ID available are random)

Dates	Number of unique ID available
2019	12
2020	15
2021	19

Thank you so much for your help!

Have a nice day!

[amitchandak]

@Anonymous , Try like

CALCULATE(CALCULATE(DistinctCOUNT(Base_PBI[Ent]),CROSSFILTER(Base_PBI[Date1],'new measure'[Date],None)),FILTER(Base_PBI,Base_PBI[Date1]<=max('new measure'[Date]) && ((Base_PBI[Date2]>DATE(YEAR(TODAY()),month(today()-1),31)) || Base_PBI[Date2]>max('new measure'[Date]))))

[Anonymous]

Thank you but it doesn't work, I still have the same false number.

Perhaps is because of the function distinctcount no? What I want is to count all my lines but one time, like if there are three times the same value in the column "ent", I want to count it as 1. Maybe Discountcount count only the value that are unique? It can be?
Thank you