topic Re: Count rows based on distinct value from another column in DAX Commands and Tips

Count rows based on distinct value from another column

DanielCarvalho — Mon, 20 Mar 2023 14:46:57 GMT

Hello Community,

I am trying to count how many rows I have each day for a time line chart but based on the distinct values of its ID column,

Basically lets say i have 2 columns , one which is the date a ticket was created and another column which is the ticket id.. As everytime a ticket changes status, the ticket is repeated in my data and another row with the same ticket id and same date is created. If i do count of my date column, for each day (in order to see how many new tickets were created each day) then it will give me innacurate results as its going to count the same ticket as many times as it is in my data.

I want to count how many rows I have in that specific date but based on distinct id.

As seen in the print above this ticket is repeated 5 times. When I count tickets created on the 28/9/22 in this case I wanted to count as 1 and not 5 as its the same ticket with the same ID.

Kind Regards,

Daniel Carvalho

Re: Count rows based on distinct value from another column

Anonymous — Tue, 21 Mar 2023 03:04:06 GMT

Hi DanielCarvalho

You can refer to the following measure

Sample data

Measure

Measure = var a=SUMMARIZE('Table','Table'[CASEID],'Table'[Date]) return COUNTAX(a,[Date])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count rows based on distinct value from another column

DanielCarvalho — Tue, 21 Mar 2023 09:13:18 GMT

Thanks for the answer!

I was wondering how to this in a MTD/YTD measure. Currently I have it like this but its not giving me the right results:

Created_Tickets(MTD) = CALCULATE(COUNT(TICKET_DASHBOARD_GAVATAR_HISTORY[ACTIONDATE_FORMATTED]),DISTINCT(TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID]),DATESMTD('CALENDAR'[DATE]))

Created_Tickets(YTD) = CALCULATE(COUNT(TICKET_DASHBOARD_GAVATAR_HISTORY[ACTIONDATE_FORMATTED]),DISTINCT(TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID]),DATESYTD('CALENDAR'[DATE], "30/9" ))

Kind Regards,

Daniel