measure that counts matching rows of a column based on the corresponding value of another column

[Anonymous]

So say i have a table like below:

ID	IDtype
1	A
2	A
3	B
4	C
5	D
6	E
7	F
8	G
9	G
10	G

i would like to create an output table as such:

ID	IDtype count
1	2
2	2
3	1
4	1
5	1
6	1
7	1
8	3
9	3
10	3

i would like to do this as a measure and not calculated column because i need it to update based on slicers. i just can't figure out how to retreive the corresponding "IDtype" for the "type" pivot coordinate using DAX. Any help is much appreciated,

thanks!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below measure.

The link to the sample pbix file is down below.

ID Type count =
VAR currentIDtype =
MAX ( 'Table'[IDtype] )
VAR newtable =
FILTER ( ALL ( 'Table' ), 'Table'[IDtype] = currentIDtype )
RETURN
COUNTROWS ( newtable )

https://www.dropbox.com/s/9py88hp4ox188h9/gsingh67.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

thanks, that seems to be working. i just had a follow-up question - i was planning to create a scatter plot ID type count vs costs (costs is a third column i omitted). So i will basically put the measure created above into the x-axis and costs (unsummarised) into the y-axis. this will work except for where there are duplicate costs values (for example if ID1 has the same costs as ID8 then the max() function will only read one of those i believe).

are there any alternatives to using max?

[Jihwan_Kim]

Hi,
Thank you for your feedback.

In my measure, what the MAX function does is to fix the current context's result and compare it with all IDs in the table.

I am not 100% sure if I understood your question correctly.

If it is OK with you, please share your sample pbix file, then I can try to come up with a more accurate measure.

thank you very much.

[Anonymous]

ah ok, thanks for explaining. In that case the issue was that when we filter 'Table'[IDtype] = currentIDtype it returns one match for each ID. This is fine for ID since it is unique. I solved my scatter plot problem by adding ID into the details. Thanks again for your help!