Forum Discussion
Distinctcount with crossfilter issue
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
4 Replies
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]))))
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
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 QinHello 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!
