topic Issue with measure in DAX Commands and Tips

Issue with measure

ValeriaBreve — Thu, 04 May 2023 09:32:22 GMT

Hello,

I am really a beginner in DAX...

I am working with a ticketing system. I have a table that has all ticket details one by one, which is fine.

I would like to build a measure so that I can make a distinct count of the tickets, but when the user selects one row of the above detailed table, it does not resolve to "1" - so the selected ticket - but calculates back the number of tickets for the location, business and year of the selected ticket.

I also have a date table that is related to my tickets table on the date (and is working correctly)

So I came up with:

Count of Tickets = CALCULATE(DISTINCTCOUNT('MIMI Tickets'[Ticket Number]),ALLEXCEPT('MIMI Tickets','MIMI Tickets'[Location],'MIMI Tickets'[Business]),ALLEXCEPT(Date_Table,Date_Table[Year],Date_Table[Date]))

But unfortunately it is not working 😞

For whatever reason the business works fine, location is fine, but when I select a year from the year slicer (based on Date_Table[Date], interaction set to filter) it does not filter anything - the count of tickets I have is actually the count of tickets for the whole timeframe.

Can somebody point me to what I am doing wrong?

Thanks!

Kind regards

Valeria

Re: Issue with measure

Mahesh0016 — Thu, 04 May 2023 09:35:22 GMT

ValeriaBreve can you please share demo data.
thank you!

Re: Issue with measure

ValeriaBreve — Thu, 04 May 2023 10:18:09 GMT

Hello, all data is confidential and creating an ex. would be very time consuming, I hoped my mistake would be evident from the measure...

I found a lead though. I have tried to use the date/year field from the tickets table directly (not the date table), and then it works. It looks like the part of my measure that is not working is related to the second ALLEXCEPT related to the datetable.

Is it not allowed to bring in a related table in ALLEXCEPT? Or to have 2 ALLEXCEPTS in the same calculate?

Thanks a lot. If it is not solvable as is, I will try to come up with mock data, but it is going to take some time.

Thanks!