SUMX - Problem with return value from count operation

[Bigglerum]

Hi

I have a Column with Chemical element symbols separated by ";" in each cell, eg [ H;O;Pb;Ti]

There are between 1 - 16 symbols in each cell from a list of 78 valid elements in another table. 

I want to create a count of these in a new column. Where the values are validated against the list of 78 elements and then counted in the column.

I have written the expression 

SUMX(

VALUES('104 Periodic Table Filter'[Element]),

CALCULATE(COUNT('201 Mineral Information'[Elements]))

This all works fine - but where the the count of elements exceeds 11 - it stays at 11.

The data is fine and I have changed the reference elements to another source - it is the same.

Maybe there is a simpler way to count without validation?

[AlexisOlson · Accepted solution]

Here's how I would count with validation using PATHCONTAINS:

ValidatedCount =
VAR CurrPath = SUBSTITUTE ( MineralInfo[ElementList], ";", "|" )
RETURN
    COUNTROWS (
        FILTER (
			PeriodicTable,
			PATHCONTAINS ( CurrPath, PeriodicTable[Element] )
		)
    )

[Bigglerum]

Thanks also - this solution works perfectly and also makes a better solution, and I can re-use the logic in other conversions - Brilliant!!!

Bigglerum

[Bigglerum]

Hi thanks for you fast reply

the first solution is the same as my original query and the second doesn't seem to fire.

If this helps anyone - I went for a less elegant solution but it works

=LEN(TRIM(Cell))-LEN(SUBSTITUTE(TRIM(Cell),";",""))+1

ie it works out on the ";" separator and adds 1 to the total

[amitchandak]

@Bigglerum , if these tables are connected correctly then one of these two should work

SUMX(
VALUES('104 Periodic Table Filter'[Element]),
CALCULATE(COUNT('201 Mineral Information'[Elements])) )

SUMX(
Summarize('104 Periodic Table Filter','104 Periodic Table Filter'[Element], "_1",
CALCULATE(COUNT('201 Mineral Information'[Elements])) ), [_1])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.