topic Re: Distinct Count with All Except and Drill Down issues in DAX Commands and Tips

Distinct Count with All Except and Drill Down issues

CSayaxang — Wed, 26 Feb 2020 16:45:37 GMT

Hello Friends,

Hoping someone can help me with my issue.

I have 2 visuals on my page:

My measure [Count tickets] is:

= CALCULATE(DISTINCTCOUNT(Tickets[Ticket#]),ALLEXCEPT(Tickets,Tickets[Cust Name]))

My first issue: The 2nd visual does not properly display my details. Example, "NY" is associated with the EAST Region but the table displays a record for ALL 4 Regions. Not sure why?

My 2nd issue: When I Drill Down using my first visual, the details does not display as expected. Example: If I click on the number "3" that represents number of Tickets from 2018 in the West region, I would expect to only see 3 records in the 2nd visual.

Any help would be great.

thanks

Re: Distinct Count with All Except and Drill Down issues

az38 — Wed, 26 Feb 2020 18:38:23 GMT

Hi CSayaxang

for the first issue replace fields State in visual: place in [state] from Region table

about second its difficult to say without look at source data and relationahip between tables.

Re: Distinct Count with All Except and Drill Down issues

CSayaxang — Wed, 26 Feb 2020 19:52:04 GMT

az38

I replaced the State field but still having issues. You don't think it has anything to do with my Measure? When I remove the [Count Tickets] measure, my second visual displays properly and the drill down works as expected.

Here's a screenshot showing the data in each table and the Relationship:

Re: Distinct Count with All Except and Drill Down issues

az38 — Wed, 26 Feb 2020 20:05:12 GMT

CSayaxang

do you use visual settings like here?

Re: Distinct Count with All Except and Drill Down issues

CSayaxang — Wed, 26 Feb 2020 20:34:25 GMT

az38

I figured it out. It was my Measure. I needed to add GROUPBY at the end in order for visual 2 to display properly. Now, it displays correctly when I drill down. Thanks for talking through this with me!

Re: Distinct Count with All Except and Drill Down issues

Anonymous — Thu, 14 Oct 2021 13:58:29 GMT

Can you show the final dax formula that worked for you? How did you modify your original formula viz.,

`= CALCULATE(DISTINCTCOUNT(Tickets[Ticket#]),ALLEXCEPT(Tickets,Tickets[Cust Name]))`

to include a `GROUPBY`?