Count # of times, for a distint value of one column, that 2+ seperate values appear in another

[Flawn]

Hello All!

I think this question is *relatively* easy but none the less I have been unable to figure out the answer!

I have an unpivoted table that looks like a much larger version of the following screenshot:



I am looking to create two measures from this table:

The first measure is a count of the number of mcs_client Ids for which two or more *distinct* Score values occur. (This count would include mcs_client ids liek the one circled in blue).

The second measure is a count of the number of mcs_client ids for which only one *distinct* Score value occurs. (This count would include mcs_client ids like the one circled in red).

I've fidged with a few different measurement designs and none seem to have produced the right count. Any insight would be greatly appreciated - and I'm happy to provide more information if needed.

Thanks Again
-Flawn

[Flawn]

Hah well that seems to have done it - turns out I was close but not quite there.

Thanks Vahid

[VahidDM]

You are welcome 🙂

[VahidDM · Accepted solution]

Hi @Flawn 

use these measures:

Measure_1 Score = 
COUNTROWS(FILTER(
		SUMMARIZE(
			'Table',
			'Table'[msc_client],
			"C", DISTINCTCOUNT('Table'[Score])
		),
		[C] = 1
	))

and for more than 1 score:

Measure>1 Score = 
COUNTROWS(FILTER(
		SUMMARIZE(
			'Table',
			'Table'[msc_client],
			"C", DISTINCTCOUNT('Table'[Score])
		),
		[C] > 1
	))

result:

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube