DAX for Value Dependent on Another Column (distinctcount)

[tmac1954]

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

[Dangar332 · Accepted solution]

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.

[FreemanZ]

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.

[tmac1954]

I had done the fill in the power query, sorry I didn't share that portion.

[FreemanZ]

then try to plot a table visual with Reported / Unreported column and a measure like:

measure = DISTINCTCOUNT(tablename[Accident #])

[Dangar332]

hi, @tmac1954 

try below three measure 

incidents = 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.

[tmac1954]

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 · Accepted solution]

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.

[tmac1954 · Accepted solution]

Works like a charm!! I appreciate you taking your time, offering a solution and replying.

Thank you!

[Dangar332]

hi, @tmac1954 

you want it in measure or table?

[tmac1954]

I was thinking a measure but either if it works.