Forum Discussion
DISTINCTCOUNT only on a certain condition
Hello,
I need to distinctcount the IDs when the status is only equal to "Pushed" or "Already pushed" (if an ID has a different status, don't count it). Here is a sample table :
| ID | Status |
| 1 | Pushed |
| 2 | Pushed |
| 2 | To do |
The result should be 1 because only one ID has the status exactly equal to "Pushed" or "Already pushed". But my current measure is wrong because it returns 2. My current measure is :
How can I get this measure to return 1 ?
Thank you
Best regards
Here is one measure expression that should work.
Only Pushed =
COUNTROWS(
FILTER(
VALUES( T3[ID] ),
ISBLANK(
CALCULATE( COUNT( T3[ID] ), NOT T3[Status] IN { "Pushed", "Already Pushed" } )
)
)
)Pat
2 Replies
- mahoneypat
Microsoft Employee
Here is one measure expression that should work.
Only Pushed =
COUNTROWS(
FILTER(
VALUES( T3[ID] ),
ISBLANK(
CALCULATE( COUNT( T3[ID] ), NOT T3[Status] IN { "Pushed", "Already Pushed" } )
)
)
)Pat
- Jihwan_Kim
Super User
Hi,
Please try the below measure, and check the attached file.
expected result: = VAR allid = VALUES ( 'Fact Assets'[ID] ) VAR idnotwant = CALCULATETABLE ( VALUES ( 'Fact Assets'[ID] ), NOT ( 'Fact Assets'[Status] IN { "Pushed", "Already pushed" } ) ) VAR onlyidwant = EXCEPT ( allid, idnotwant ) RETURN COUNTROWS ( onlyidwant )
