topic Count value occurences of a column group by the ID in DAX Commands and Tips

Count value occurences of a column group by the ID

Gorloot — Wed, 05 Jun 2024 23:15:52 GMT

Hi,

Please see below for example,

I would like to create a measure that counts how many times equipment has a certain system fixed.

The count can become larger/smaller by the date filter. The counting should not be affected by the Work order Nbr.

The purpose is to see how many times that the same system repaired appears by equipment ID in a date range.

Thank you.

Equipment ID	Work order Nbr	WO date created	System repaired	expected output : Count of system repaired
1	12345	2/9/2024	Tire	1
2	12346	2/10/2024	air condition	3
2	12333	1/10/2024	air condition	3
2	12386	4/10/2024	air condition	3
2	12346	2/10/2024	plug	2
2	12355	3/10/2024	plug	2
3	12347	2/10/2024	light	1
3	12347	2/10/2024	window	1
4	12348	2/13/2024	window	2
4	12348	2/13/2024	window	2
4	12348	2/13/2024	Tire	1
5	12349	2/15/2024	Engine	3
5	12349	2/15/2024	Engine	3
5	12350	2/16/2024	Engine	3

Re: Count value occurences of a column group by the ID

Anonymous — Thu, 06 Jun 2024 01:33:38 GMT

Hi Gorloot

Please try the following measure:

Count of System Repaired = CALCULATE( COUNTA('Table'[System repaired]), ALLEXCEPT('Table', 'Table'[Equipment ID],'Table'[System repaired]), ALL('Table'[Work order Nbr]) )

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count value occurences of a column group by the ID

Gorloot — Thu, 06 Jun 2024 01:42:47 GMT

Hi Anonymous ,

Thanks for quick response. I think this is very close to the solution.

However, I do have a bigger dataset and the measure is having a problem of grabbing other "System Repaired" counts from outside "WO created" date range.

I wish to grab the counts that are only limited to desired date range.

Thank you again.

Re: Count value occurences of a column group by the ID

Anonymous — Thu, 06 Jun 2024 08:36:41 GMT

Hi Gorloot

Try the following measure:

Count of System Repaired = VAR _mindate = MIN('Table'[WO date created]) VAR _maxdate = MAX('Table'[WO date created]) RETURN CALCULATE( COUNTA('Table'[System repaired]), FILTER(ALLEXCEPT('Table', 'Table'[Equipment ID],'Table'[System repaired]), SELECTEDVALUE('Table'[WO date created])>= _mindate && SELECTEDVALUE('Table'[WO date created])<= _maxdate) )

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.