Forum Discussion
DAX for Value Dependent on Another Column (distinctcount)
Hello, I appreciate any help for creating a measure for the following. There may be multipe row entries for one accident #.
Thank you.
| Result should be: |
| 23 incidents |
| 21 reported |
| 2 unreported |
| Accident # | Reported / Unreported |
| 2023-001 | Reported |
| Reported | |
| 2023-002 | Reported |
| Reported | |
| 2023-003 | Reported |
| 2023-004 | Reported |
| Reported | |
| 2023-005 | Reported |
| Reported | |
| 2023-006 | Reported |
| Reported | |
| 2023-007 | Reported |
| 2023-009 | Reported |
| Reported | |
| 2023-013 | Reported |
| Reported | |
| 2023-014 | Reported |
| Reported | |
| 2023-015 | Reported |
| Reported | |
| 2023-016 | Unreported |
| Unreported | |
| 2023-017 | Reported |
| Reported | |
| 2023-018 | Reported |
| 2023-020 | Reported |
| Reported | |
| 2023-025 | Reported |
| 2023-026 | Unreported |
| Unreported | |
| Unreported | |
| 2023-029 | Reported |
| Reported | |
| Reported | |
| Reported | |
| 2023-046 | Reported |
| Reported | |
| 2023-047 | Reported |
| Reported | |
| 2023-068 | Reported |
| Reported | |
| 2023-073 | Reported |
| 2023-083 | Reported |
| Reported |
hi, tmac1954
my above code only work without filling data
because i ignore blank value in measure.
so try below code it help you
it's all measure
incidents = DISTINCTCOUNT(accident[Accident #]) reported = CALCULATE( DISTINCTCOUNT(accident[Accident #]), accident[Reported / Unreported]="reported" ) unreported = CALCULATE( DISTINCTCOUNT(accident[Accident #]), accident[Reported / Unreported]="unreported" )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.
Works like a charm!! I appreciate you taking your time, offering a solution and replying.
Thank you!
9 Replies
I was thinking a measure but either if it works.
- Dangar332
Resident Rockstar
hi, tmac1954
try below three measureincidents = COUNTX( FILTER(accident, accident[Accident #]<>BLANK()), accident[Accident #] ) reported = COUNTX( FILTER(accident, accident[Accident #]<>BLANK()&& accident[Reported / Unreported]="reported" ) ,accident[Accident #] ) unreported = COUNTX( FILTER(accident, accident[Accident #]<>BLANK()&& accident[Reported / Unreported]="unreported" ) ,accident[Accident #] )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.
Thank you for the suggestions and examples. I'm still receiving the incorrect values. I used the "fill down" in the accident #'s in my power query and counting the disctinctcount for total accidents. The issue is there is multiple row entries for accidents showing reported or unreported and it's counting one accident reported/unreported multipe times.
I believe I'm not understanding the logic, sorry.. I appreciate your help very much!!
I am still learning.
Reported =COUNTX(FILTER(Accidents,[Accident #]<>BLANK()&& Accidents [Reported / Unreported]="reported"),Accidents[Accident #])Unreported =COUNTX(FILTER(Accidents,[Accident #]<>BLANK()&& Accidents [Reported / Unreported]="unreported"),Accidents[Accident #])- Dangar332
hi, tmac1954
my above code only work without filling data
because i ignore blank value in measure.
so try below code it help you
it's all measure
incidents = DISTINCTCOUNT(accident[Accident #]) reported = CALCULATE( DISTINCTCOUNT(accident[Accident #]), accident[Reported / Unreported]="reported" ) unreported = CALCULATE( DISTINCTCOUNT(accident[Accident #]), accident[Reported / Unreported]="unreported" )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.
FreemanZSuper User
You need firstly fill down the Accident # column in Power Query:
https://learn.microsoft.com/en-us/power-query/fill-values-column
then everything becomes apparent.