topic Re: Count(distinct) not working in DAX Commands and Tips

Count(distinct) not working

avujica — Thu, 20 Aug 2020 00:00:03 GMT

Hi I'm having trouble getting Count(distinct) to work. I have a table that i would like to get a count of the distinct project numbers and the total of lab hours associate with each distinct project #. I can't seem to even get just the distinct count of project #'s working and i would expect to get 14 but all i get ins continuous calculating. I have used both the built in selection within the lab project number field in the values table and I also tried to write my own measure with no luck. Can anyone provide any insight and help ? Thank you very much for your help and consideration.

Lab Project Number	Test Number	Total Lab Hours Used (Test)	Test Received Week	Test Completed Week
1000706742	11708536	1	10	10
1000708539	10494986	2	10	15
1000785404	11607568	6	10	21
1000785404	11753139	6	10	21
1000788016	11777390	1	10	12
1000788016	11777391	1	10	12
1000788016	11777392	0.1	10	12
1000819703	11737232	1	10	10
1000819703	11737234	1	10	10
1000819703	11737235	1	10	10
1000862033	11125725	0	10	24
1000862204	11774150	0	10	11
1000868875	11772064	7	10	15
1000868875	11772082	7	10	15
1000868875	11772107	7	10	15
1000868875	11772108	7	10	15
1000868875	11772109	7	10	15
1000898947	11583964	2	10	19
1000898947	11583966	2	10	19
1000898947	11583967	2	10	19
1000898947	11583969	2	10	19
1000900519	11745995	3	10	13
1000903827	11642455	3	10	11
1000903827	11642456	3	10	11
1000907753	11734499	2	10	11
1000907753	11735755	2.5	10	11
1000914566	11774034	6	10	12
1000994554	11782841	5	10	12
1000994554	11782843	5	10	12
1000994554	11782845	5	10	12
1000994554	11782846	5	10	12
1000994554	11783074	2	10	12

Re: Count(distinct) not working

Greg_Deckler — Thu, 20 Aug 2020 00:35:36 GMT

avujica - The built-in distinct count works for me. See attached PBIX below sig. You want table 3 page 3

Re: Count(distinct) not working

amitchandak — Thu, 20 Aug 2020 04:00:11 GMT

avujica , You should be able to get is using

distinctcount(Table[Lab Project Number]), But as the projects are repeating, grand total will not sum up in case you are checking across some other axis/group

And may observe differences at line level avg and overall average.

Can you share sample output in a table format, if the issue not resolved by the last update from Greg?

Re: Count(distinct) not working

avujica — Thu, 20 Aug 2020 16:28:59 GMT

amitchandak , Greg_Deckler

Hi, thank you very much for the quick replies and taking time to look at my problem. The command works fine if I were to run it on a seperate standalone .xls table but it doesnt run within the report I built. It may be a problem of it looking at my entire data cube/data set rather than just the info that i have filtered to my table and i dont know how to get around that. If i run the command in a seperate (card) visualization it comes back with 1.02 million.. I do not have any page level or report level filters just the 5 visual level filters and values you see in the table headings. I am also running 4 slicers for test recievd week, test completed week, and 2 lab identifiers.

Thank you for your help !

Re: Count(distinct) not working

avujica — Tue, 25 Aug 2020 21:54:52 GMT

Hi Thank you for your help but your solution did not help. If you have time please see the post i made in this thread regarding my observations.

Thank you again for your help.

Re: Count(distinct) not working

avujica — Tue, 25 Aug 2020 21:57:17 GMT

Hi Thank you for lookinbg at my problem and offering a solution unfortunately its not working as intended. Please note my observation documente din my most recent post in this thread.

Thank you again for your help,

Alan

Re: Count(distinct) not working

edhans — Tue, 25 Aug 2020 22:48:12 GMT

avujica hover over the filter icon on the visual when i the desktop and confirm it is filtered as you think it is. I've filter panes and off-paged syncing with hidden slicers on current page messing with filters during DAX debugging.

Other than that, you will need to share something with us. You can have filtering happening depending on your filter relationships. Bi-directional possibly, but Many to many will mess with this. Otherwise it will be very difficult for any of us to figure out the problem by your description alone. On its own, DISTINCTCOUNT() is a pretty basic function, so anything going wrong is a filter or modeling issue, or both.