topic DAX for Value Dependent on Another Column (distinctcount) in DAX Commands and Tips

DAX for Value Dependent on Another Column (distinctcount)

tmac1954 — Thu, 26 Oct 2023 13:40:50 GMT

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

Re: DAX for Value Dependent on Another Column (distinctcount)

Dangar332 — Thu, 26 Oct 2023 13:54:43 GMT

hi, tmac1954

you want it in measure or table?

Re: DAX for Value Dependent on Another Column (distinctcount)

Dangar332 — Thu, 26 Oct 2023 14:04:54 GMT

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.

Re: DAX for Value Dependent on Another Column (distinctcount)

FreemanZ — Thu, 26 Oct 2023 14:06:04 GMT

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.

Re: DAX for Value Dependent on Another Column (distinctcount)

tmac1954 — Thu, 26 Oct 2023 14:13:30 GMT

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

Re: DAX for Value Dependent on Another Column (distinctcount)

FreemanZ — Thu, 26 Oct 2023 14:20:16 GMT

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

measure = DISTINCTCOUNT(tablename[Accident #])

Re: DAX for Value Dependent on Another Column (distinctcount)

tmac1954 — Thu, 26 Oct 2023 14:30:23 GMT

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 #]

)

Re: DAX for Value Dependent on Another Column (distinctcount)

tmac1954 — Thu, 26 Oct 2023 14:48:43 GMT

I was thinking a measure but either if it works.

Re: DAX for Value Dependent on Another Column (distinctcount)

Dangar332 — Thu, 26 Oct 2023 15:07:07 GMT

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.

Re: DAX for Value Dependent on Another Column (distinctcount)

tmac1954 — Thu, 26 Oct 2023 15:20:10 GMT

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

Thank you!