Forum Discussion
Status Count with Different ID number
Hi,
We export and databas in a Excel file from a Task Manager System called Bitrix24, every task has an ID Number I just noticed that for any major change you do in the task will duplicate the data with the same ID number but with any changes like a deadline or date change.
I need to be able to count status but only those with different ID number so in one comlum I have the ID number and 2nd Column the status, it may be same ID number twice with the same status but differente date or name who is responsable for that task so when im trying to count the status is giving me wrong data.
So i need to count status only those counlums that has a different ID number.
How can i do this?
create a measure called
Distinct Case Count = DISTINCTCOUNT(Table[Id])
Add following to table visual:
- Status
- Distinct Case count (new measure whcih is creted)
and you will get the result as expected, try this.
5 Replies
Hi,
We export and databas in a Excel file from a Task Manager System called Bitrix24, every task has an ID Number I just noticed that for any major change you do in the task will duplicate the data with the same ID number but with any changes like a deadline or date change.
I need to be able to count status but only those with different ID number so in one comlum I have the ID number and 2nd Column the status, it may be same ID number twice with the same status but differente date or name who is responsable for that task so when im trying to count the status is giving me wrong data.
So i need to count status only those counlums that has a different ID number.
How can i do this?
Any suggestions?
- parry2k
Super User
why you cannot use distinctcount(table[id])
Hi,
Im new in DAX but I can have the same ID number with diffent status for example:
This is my database if you noticed ID number can be the same but responable name is diffente i will have statistics of how many task i have Open Closed and Pending:
ID: 1234 Status: Open Date: 01/01/17 Responsable: John Smith
ID: 1234 Status: Open Date: 02/01/17 Responsable: Joe Mars
ID: 1235 Status: Closed Date 01/01/17 Reponsable: John Smith
ID: 1237 Status: Closed Date 01/01/17 Reponsable: John Smith
ID: 1236 Status: Pending Date 01/01/17 Responsable Joe Mars
ID: 1236 Status: Pending Date 01/01/017 Responable John Smith
The right statistics for is the followingOpen= 1 (If i do a count based on status answer will be 2 but thats incorrect, I have only one Task open with the same ID)
Closed = 2
Pending = 1
How can I count the status to give me the right answer?
