topic Re: Open and Closed : Count in DAX Commands and Tips

Open and Closed : Count

Chandrashekar — Tue, 11 Jan 2022 11:46:42 GMT

Hello Team,

Can you please help me in getting below counts. Data should change based on slicers(File Attached).

1. Open and closed count

2. Percentage ticket closed.

PBIX FIle

Regards,

Chandrashekar B

Re: Open and Closed : Count

amitchandak — Tue, 11 Jan 2022 11:49:20 GMT

Chandrashekar , I have blog on similar topic, see if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Yet to check the file

Re: Open and Closed : Count

Chandrashekar — Tue, 11 Jan 2022 11:57:58 GMT

Hello,

I saw your post but have different requirement where I do not have closed date field.

Regards,

Chandrashekar B

Re: Open and Closed : Count

amitchandak — Tue, 11 Jan 2022 12:12:23 GMT

Chandrashekar , Try following measures

Open count = countx(FILTER('Table', 'Table'[Status] ="Open"), [Ticket]) Closed count = countx(FILTER('Table', 'Table'[Status] ="Closed"), [Ticket]) Close % = divide([Closed count], COUNTX('Table', [Ticket]))

Re: Open and Closed : Count

smpa01 — Tue, 11 Jan 2022 13:43:20 GMT

Chandrashekar you need a schema like this

and three measures as follows

_Closed = CALCULATE(COUNT('Table'[Ticket]),FILTER(VALUES('Table'[Status]),'Table'[Status]="Closed")) _Open = CALCULATE(COUNT('Table'[Ticket]),FILTER(VALUES('Table'[Status]),'Table'[Status]="Open")) _closedPCT = DIVIDE([_Closed],COUNTROWS('Table'))

Re: Open and Closed : Count

Chandrashekar — Wed, 12 Jan 2022 05:00:18 GMT

Hello,

Thanks it is working fine. May I know purpose of using underscore with variable.

Regards,

Chandrashekar B

Re: Open and Closed : Count

Chandrashekar — Wed, 12 Jan 2022 05:01:58 GMT

Hello,

Thanks it is working fine.

Regards,

Chandrashekar B