Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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 !
@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?
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.
@avujica - The built-in distinct count works for me. See attached PBIX below sig. You want table 3 page 3
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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting